bold Counting Records

[SimRick]

I want to alter the following query so that it only returns records for those [Resident Involved 1] who have more than 3 (or any number) records during the date range. Can anyone help? THANKS!

Red TextSELECT tblmedreport.[Report Number], tblmedreport.House, tblmedreport.[Report Date], tblmedreport.Type, tblmedreport.Comment1, tblmedreport.Reason, tblmedreport.[Resident Involved 1], tblmedreport.[Employee Involved 1], [Please Enter A Start Date] AS Expr1, [Please Enter A Finish Date] AS Expr2
FROM tblmedreport
WHERE (((tblmedreport.House)=[Please Enter A House Name]) AND ((tblmedreport.[Report Date]) Between [Please Enter A Start Date] And [Please Enter A Finish Date]))
ORDER BY tblmedreport.[Report Date];

 

[mgolla]

Change your SQL Statement to this and try it out:

SELECT tblmedreport.[Report Number], tblmedreport.House, tblmedreport.[Report Date], tblmedreport.Type, tblmedreport.Comment1, tblmedreport.Reason, tblmedreport.[Resident Involved 1], tblmedreport.[Employee Involved 1]FROM tblmedreport
WHERE (((tblmedreport.House)=[Please Enter A House Name]) AND ((tblmedreport.[Report Date]) Between [Please Enter A Start Date] And [Please Enter A Finish Date])
GROUP BY [Report Number],[House],[Report Date],Type,Comment1,Reason,[Resident Involved 1],[Employee Involved 1]
HAVING (Count([Resident Involved 1])>=3)
ORDER BY tblmedreport.[Report Date]

HTH
Mike

 

[SimRick]

Thanks for the response...I get a syntax error - missing operator when I run it.

 

[tekkyun]

Isn't this a case of switching on the totals for your query and setting a new field for the count of [resident involved 1] with a criteria of >3?

eg
SELECT tblmedreport.[Report Number], tblmedreport.House, tblmedreport.[Report Date], tblmedreport.Type, tblmedreport.comment1, tblmedreport.reason, tblmedreport.[Resident Involved 1], tblmedreport.[employee involved 1], [Please Enter A Start Date] AS Expr1, [Please Enter A Finish Date] AS Expr2
FROM tblmedreport
GROUP BY tblmedreport.[Report Number], tblmedreport.House, tblmedreport.[Report Date], tblmedreport.Type, tblmedreport.comment1, tblmedreport.reason, tblmedreport.[Resident Involved 1], tblmedreport.[employee involved 1], [Please Enter A Start Date], [Please Enter A Finish Date]
HAVING (((tblmedreport.House)=[Please Enter A House Name]) AND ((tblmedreport.[Report Date]) Between [Please Enter A Start Date] And [Please Enter A Finish Date]) AND ((Count(tblmedreport.[Resident Involved 1]))>2))
ORDER BY tblmedreport.[Report Date];

 

[tekkyun]

Isn't this a case of switching on the totals for your query and setting a new field for the count of [resident involved 1] with a criteria of >2?

eg
SELECT tblmedreport.[Report Number], tblmedreport.House, tblmedreport.[Report Date], tblmedreport.Type, tblmedreport.comment1, tblmedreport.reason, tblmedreport.[Resident Involved 1], tblmedreport.[employee involved 1], [Please Enter A Start Date] AS Expr1, [Please Enter A Finish Date] AS Expr2
FROM tblmedreport
GROUP BY tblmedreport.[Report Number], tblmedreport.House, tblmedreport.[Report Date], tblmedreport.Type, tblmedreport.comment1, tblmedreport.reason, tblmedreport.[Resident Involved 1], tblmedreport.[employee involved 1], [Please Enter A Start Date], [Please Enter A Finish Date]
HAVING (((tblmedreport.House)=[Please Enter A House Name]) AND ((tblmedreport.[Report Date]) Between [Please Enter A Start Date] And [Please Enter A Finish Date]) AND ((Count(tblmedreport.[Resident Involved 1]))>2))
ORDER BY tblmedreport.[Report Date];

 

[SimRick]

Thanks - the first query returned all records, as the [resident involved 1] field, which contains a name, always has a total value of 1. The second of your ideas generated an "Invalid SQL SDtatement - expected Delete, Insert, Procedure, Select or Update" message. I've tried many ways to count totals, but have been unsuccessful.

 

[mgolla]

Sorry didn't pay too close attention:

SELECT tblmedreport.[Report Number], tblmedreport.House, tblmedreport.[Report Date], tblmedreport.Type, tblmedreport.Comment1, tblmedreport.Reason, tblmedreport.[Resident Involved 1], tblmedreport.[Employee Involved 1]FROM tblmedreport
GROUP BY [Report Number],[House],[Report Date],Type,Comment1,Reason,[Resident Involved 1],[Employee Involved 1]
HAVING (((tblmedreport.House)=[Please Enter A House Name]) AND ((tblmedreport.[Report Date]) Between [Please Enter A Start Date] And [Please Enter A Finish Date])AND(Count([Resident Involved 1])>=3))
ORDER BY tblmedreport.[Report Date]

 

[SimRick]

I'm not sure why, but this query did not return any records even though I verified that it should return about 120 given the parameters I entered. Thanks for the ideas!