Count Unique Records in Query

[SimRick]

I have the following query:

SELECT tblreport.[Resident Involved 1], tblreport.[Report Number], tblreport.[Report Date]
FROM tblreport
GROUP BY tblreport.[Resident Involved 1], tblreport.[Report Number], tblreport.[Report Date]
HAVING (((tblreport.[Report Date]) Between Date() And Date()-30));

I need to display those records where a unique [Resident Involved 1) has three or more records returned in the query. Can anyone help?
Thanks

 

[lespaul]

SELECT tblreport.[Resident Involved 1], tblreport.[Report Number], tblreport.[Report Date]
FROM tblreport
WHERE (((tblreport.[Report Date]) Between Date() And Date()-30));
GROUP BY tblreport.[Resident Involved 1], tblreport.[Report Number], tblreport.[Report Date]
HAVING Count([Resident Involved 1]) > 2


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[lespaul]

pressed submit too soon:

SELECT tblreport.[Resident Involved 1], tblreport.[Report Number], tblreport.[Report Date]
FROM tblreport
WHERE tblreport.[Report Date] Between Date() And (Date()-30)
GROUP BY tblreport.[Resident Involved 1], tblreport.[Report Number], tblreport.[Report Date]
HAVING Count([Resident Involved 1]) > 2



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[SimRick]

Thanks, but it didn't work. it should return 1 [Resident Involved 1] who had 3 reports, but didn't return any.

 

[PHV]

And this ?
SELECT A.[Resident Involved 1], A.[Report Number], A.[Report Date]
FROM tblreport AS A INNER JOIN (
SELECT [Resident Involved 1] FROM tblreport
WHERE [Report Date] Between Date() And (Date()-30)
GROUP BY tblreport.[Resident Involved 1] HAVING Count(*)>2
) AS B ON A.[Resident Involved 1] = B.[Resident Involved 1]
WHERE A.[Report Date] Between Date() And (Date()-30);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SimRick]

Perfect, Thanks a lot. This forum is irreplaceable!