I need to query the field name of "true" value out of 3 fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

On a dental screening form for kindergardeners, I have 3 fields (Class I
Cavities, Class II Cavities, Class III Cavities) with True/False datatype
(check box on form).

In any given record, all three fields may be False (no cavaties...) however
no more than one can be True, (when cavaties are present, are you with me?)

My question is this. How can I set up a query that tells me only the one
field name where value is "True", so I can build it into a report for each
kid?

I didn't design this database. If I had I would have made one field called
"Severity", with a combo box with Class 1-III (and none).

The tooth fairy will smile upon you if you can help me out of this little
jam. Thanks!
 
Vida SF said:
On a dental screening form for kindergardeners, I have 3 fields (Class I
Cavities, Class II Cavities, Class III Cavities) with True/False datatype
(check box on form).

In any given record, all three fields may be False (no cavaties...) however
no more than one can be True, (when cavaties are present, are you with me?)

My question is this. How can I set up a query that tells me only the one
field name where value is "True", so I can build it into a report for each
kid?

I didn't design this database. If I had I would have made one field called
"Severity", with a combo box with Class 1-III (and none).
Click to expand...


You can use a calculated field in your query

Severity: Switch([Class I Cavities],"Class I Cavities",
[Class II Cavities],"Class II Cavities", [Class III
Cavities],"Class III Cavities", True."No Cavities")
 
If you have your query set up with three separate fields: Class I Cavity,
Class II Cavity and so on, and ALL YOU WANT are the records where one of
those boxes are checked for that client then type "Yes" in the Criteria
section for each of the three fields and try running your query. I should
omit any records where the boxes have NOT been checked. Sometimes you may
have to use something like "Is Not Null" in otherwords you want to see only
the records that do NOT have empty or bank checkboxes but I think you get the
jist. Use the help and type in Null or Is not null something like that if
what I said does not work.

Hope this helps!!
 
Back
Top