Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    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.

    Regards
    David

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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
  •