Checklist Query

[Chip]

Hi, I am having a problem writing a query for a table that contains a safety
checklist for different facilities with about 25 fields of a checklist FORM
that is comprised of 3-position radio buttons with the values of 1, 2, or 3.
Now my problem, I have built the form to where when the radio button for
the value '2' is selected that it is always a 'bad' answer for the checklist
and I want to build a query to populate a report that will only show all the
facilities bad answers (as in the fields that were selected as bad) so that
the safety person only sees the bad items for ALL facilities that need to be
corrected. Basically, the report will show Facility A, then only show the
bad answers for that facility, then Facility B and its bad answers and so
on. In the Query Design mode, it is only allowing me about 8 "or"
statements under 8 fields and I have about 25 fields that would need an 'or'
statement. I am not sure what to do at this point. I am relatively new to
ACCESS and databases for that matter so this may be a simple fix, or it may
not be possible the way it is set up. Any help would be greatly
appreciated!!!! And as always, THANKS IN ADVANCE!!!!

 

[John Spencer (MVP)]

You can add more rows the criteria section by Choosing INSERT: Rows from the
menu bar.

That fixes your immediate problem, but what you really have is a design problem
in your tables. You should have a Facilities Table and a Safety Issues and a
FacilityIssues table and then relate them. This will make your life a lot
easier in the long run.

TblFacilities
FacilityID
FacilityName
FacAddress
....

tblSafetyIssues
IssueID
IssueName

tblFacilityIssues
FacilityID
IssueID
Score (1,2, or 3)

With this set up and the proper forms and subforms, the query becomes something
much simpler.

SELECT FacilityName, IssueName, Score
FROM (TblFacilities INNER JOIN TblFacilityIssues
ON tblFacilities.FacilityID = tblFacilityIssues.FacilityID)
INNER JOIN tblSafetyIssues ON
tblFacilityIssues.IssueID = TblsafetyIssues.IssueID
WHERE Score = 2

In addition, you could easily count the Issues across facilities, calculate
average scores, count the number of 1, 2, or 3 scores per facility, etc.