Record county by status

[Annette]

I have a query that will return an InspectionID, Question# and
Outcome. Based on the Question#, I need to categorize the outcomes by
InspectionID. A category is a group of questions. So questions 1,2,3
are CDC Risk Factor group, questions 4,5,6,7 may be Cross
Contamination group, etc.

For example, I have three InpectionID's (123, 456, and 789). Each
inspection has outcomes to 27 questions. So for inspection 123, there
are 27 questions in the inspection that have outcomes of IN compliance
or OUT of compliance.

I have been tasked to come up with a result by category of the total
investigations and outcome. Once any question in the category is OUT
then the result of that investigation by category is OUT of
compliance.

Let's say inspection 123 has question 1 IN compliance, question 2 IN
compliance, and question 3 IN compliance. The # of IN compliance for
inspection 123 for category CDC Risk Factor is 1 and the total # of
OUT of compliance is 0. If inspection 123 has question 1 IN
compliance, question 2 IN compliance and question 3 OUT of compliance,
then the # of IN compliance for inspection 123 for the CDC Risk Factor
is 0 and the total # of OUT of compliance is 1.

This is so confusing to explain. Sorry.

My question is how do I gather this information through query? I can
determin category grouping based on questions, I can count the total #
of investigations, but I don't know how to get a count of IN vs OUT
when there are multiple questions and as soon as there is one record
OUT of compliance the whole category group for that inspection is OUT
of compliance.

 

[John Spencer]

You could use a SUB-query in the SELECT clause of the query to determine
compliance for the group.

Exists(SELECT * FROM Investigations as TEMP WHERE Compliance="Out" and
TEMP.InvestigationID = Investigations.InvestigationID) as OutOfCompliance

Without knowing more about your table structure it is hard to give you an
exact query solution.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County