Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    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.


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts
    You could create a query based on the table and add a calculated column

    Combined: Abs(1*[fldA]+2*[fldB]+4*[fldC]+8*[fldD]+16*[fldE]+32*[fldF]+64*[fldG])

    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts