Count of inspections

[Akilah]

How can I form a query that collects info based on the amount of times it
appears in the table. I want it to return inspection number, inspector name,
inspection state where inspection number is in the table 3 or more times. I
think it has something to do with count and aggregate functions but I can't
get it to work right. Please help. Thanks

 

[John Spencer]

SELECT *
FROM YourTable
WHERE [Inspector Number] in
(SELECT [Inspector Number]
FROM YourTable
GROUP BY [Inspector Number]
HAVING Count(*) >2)

You can use the find duplicates query wizard to build the query you want and
then change the subquery in the criteria from ">1" to ">2".

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[KARL DEWEY]

Try these two queries ---
Akilah_1 ----
SELECT Akilah.[inspection number]
FROM Akilah
GROUP BY Akilah.[inspection number]
HAVING (((Count(Akilah.[inspection number]))>=3));

SELECT Akilah.*
FROM Akilah INNER JOIN Akilah_1 ON Akilah.[inspection number] =
Akilah_1.[inspection number];