Newbie Question: How to build a query to find every record where condition is met.

[bennettdon1546]

Apologies for the newbie question!

I am trying to build a query that will find, count and sort records based on meeting a simple condition. My data consists of a list of firm numbers with corresponding inspection numbers. Each firm has inspections. Each inspection has an outcome based on the severity of the findings; Official Action (OAI), Voluntary Action (VAI), or No Action (NAI).

The basic premise is that firms with inspections that are deemed OAI should be monitored - and those firms should get a follow-up inspection to verify all is well. I am not certain how to build a query that will look at all the records and find firms where at least ONE of its associated inspections was OAI but still show me its other inspections as well.

I have tried to articulate this below along with some examples:


Database Variables:
Firm Number – unique identifier for each firm.
Inspection Number – unique identifier for each inspection.
Outcome – inspection outcome severity code (OAI, VAI, NAI).

Business questions:

1. How many firms have had at least one inspection where the outcome was OAI?
2. Out of that set, how many firms have had an inspection AFTER the inspection which resulted in OAI?
3. Out of that set, how many firms have had yet ANOTHER inspection, and what is the frequency for each outcome possibility (OAI, VAI, NAI)?
4. Count the number of days between each inspection for each scenario in #2 and #3 above.

Examples:
Firm with NO OAI Outcome

Firm Number	Insp ID	Outcome	Inspection Date
252586	80357	VAI	10/15/12
252586	84888	VAI	08/26/13
252586	97372	VAI	07/05/16

Firm with Inspection Outcome of OAI And Follow-Up Inspection

Firm Number	Insp ID	Outcome	Inspection Date
254952	82500	OAI	04/10/13
254952	86779	OAI	04/21/14

Firm with Inspection Outcome of OAI And Follow-Up Inspection is NOT OAI

Firm Number	Insp ID	Outcome	Inspection Date
255074	80299	OAI	11/08/12
255074	80299	VAI	07/29/13

[Bulzie]

Will try to get you started. Using Query Design, Create Query1, add FirmNumber and Outcome and in criteria box for Outcome add "OAI". Click on Totals "GroupBy" for both fields to remove dups. This should get you all FirmNumber that have one of those OAI records. Now create Query2, add in Query1 and your table, link them by FirnNumber and add all fields from Table1. When you run this it should give you only those Firms that have at least one record as OAI. Now you can use Query2 in other queries to get your totals and such.

[bennettdon1546]

Exactly what I needed - just a good kick start. I sincerely appreciate the help.