Results 1 to 2 of 2
2009-11-09, 15:32 #1
- Join Date
- Jun 2002
- Nottingham, Nottinghamshire, United Kingdom
- Thanked 0 Times in 0 Posts
I am writing a database and am having difficulty in deciding how to proceed with handling one aspect of the data. A table has (amongst many other fields) a set of 7 true/false fields indicating the type of property it relates to; call them fldA through fldG. A property may have 1 of the fields set true or several (but not none).
At the moment this set of fields are fine for showing data once the property has been selected, but I'd now like to be able to print reports etc based on a choice of the fields. The options would be (1) selection by the presence of a single true field - easy to code as a query; (2) selection of more than one true field - gets more complicated but can be achieved with complex AND statements; and (3) selection on some true fields but excluding other fields - e.g. fldA = true AND fldC = true BUT fldB = false.
Are there better ways of dealing with data requirements of this sort? I'd thought of a byte field and can see how that would work with (1) and (2) but I can't get my head round how it would deal with the combinations in (3). Any help or a pointer towards appropriate articles, etc would be appreciated.
2009-11-09, 15:52 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
You could create a query based on the table and add a calculated column
In the value of Combined, a True for fldA contributes 1, a True for fldB contributes 2, a True for fldC contributes 4, etc.
For example, if you want to test whether fldA = True and fldC = True and fldB = False, you'd check that Combined And (1 + 2 + 4) = (1 + 4)