Yes no queries

[josephw71]

Please bear with me, I'm self taught, winging this whole thing. I work at a clinic and I need to track lab tests, not the results, just if they did them, so yes or no. There are 5 tests and I need to know if they are missing any single one of them, and which ones they have not done. So did they skip 1,2, and 4, or skip just 3. If they've done all 5 I don't care. My solution was this queries:
test 1, 2, 3, 4, 5
criteria f, f, f, f, f
or f, t, t, t, t
t, f, t, t, t
t, t, f, t, t
You get the idea, I hope. It works but people tend to do all there labs, or none of them, and I suspect this may not work for more complicated combinations, any ideas, suggestions?



Forgive me if I'm not in the right forum topic or make no sense at all, and any advice is appreciated.

[ranman256]

can you make query Q1, to get the bad records:
select KeyID, iif([fld1]='f' or [fld2]='f' or [fld3]='f' or [fld4]='f' or [fld5]='f',"incomplete","done") as Status from table

then make Q2 to pull the entire record based on Q1 to see which items were not done:
select table.* from table,Q1 where Q1.KeyID = table.KeyID and Status = 'Incomplete'"

[ssanfu]

Alternate method...... (but not knowing what you are looking for).

It sounds like you do not have a normalized table structure if you have fields named "test1, test2, test3, test4, test5".
If this is your structure, you could use a form with text box controls bound to the Yes/No fields, Set the format of the text box to "Yes/No", then set conditional formatting to have a rec background if the value is False/No/0.