Query trouble...summing count...suggestions?

[allie357]

=Sum([CountOfPolicy Violated]) is a control on my report

It gives me the total number of violations per person. I need to write
a query that allows me to run a report that shows people with over x
total violations (there are different violations but they are all in
one field Policy Violated and the Count counts them.

This is my current SQL for the query that shows the report. It shows
each violation for each person grouped by RC and Department Name. What
I need to do is evaluate them by total violations per person. Any help
is appreciated...

SELECT tbl_Violations![Violator's Last Name] & ", " &
tbl_Violations![Violator's First Name] AS [Violator's Name],
Count(tbl_Violations.[Policy Violated]) AS [CountOfPolicy Violated],
tblDepartments.[RC Name], tblDepartments.[Dept Name],
tbl_Violations.[Policy Violated], tblDepartments.[Dept Number],
tbl_Violations.Amount, tbl_Violations.Details,
tbl_Violations.[Violator's Last Name]
FROM tbl_Violations LEFT JOIN tblDepartments ON
tbl_Violations.[Violator's Department Number] = tblDepartments.[Dept
Number]
WHERE (((tbl_Violations.[Date Entered]) Between [Enter Start Date
(mm/dd/yyyy)] And [Enter End Date (mm/dd/yyyy)]))
GROUP BY tbl_Violations![Violator's Last Name] & ", " &
tbl_Violations![Violator's First Name], tblDepartments.[RC Name],
tblDepartments.[Dept Name], tbl_Violations.[Policy Violated],
tblDepartments.[Dept Number], tbl_Violations.Amount,
tbl_Violations.Details, tbl_Violations.[Violator's Last Name]
HAVING (((tblDepartments.[RC Name])=[Enter RC Name]))
ORDER BY tbl_Violations![Violator's Last Name] & ", " &
tbl_Violations![Violator's First Name], Count(tbl_Violations.[Policy
Violated]) DESC , tbl_Violations.[Policy Violated]
WITH OWNERACCESS OPTION;

 

[guido via AccessMonster.com]

Create a new query that calculates just the desired people. If first and last
name are the key to a person use those two fields (or and id if you have one).
Put the person and count of violations with the date where clause. The SQL
would be something like:
SELECT tbl_Violations![Violator's Last Name],
tbl_Violations![Violator's First Name],
Count(tbl_Violations.[Policy Violated]) AS [CountOfPolicy Violated]
FROM tbl_Violations
WHERE (((tbl_Violations.[Date Entered]) Between [Enter Start Date
(mm/dd/yyyy)] And [Enter End Date (mm/dd/yyyy)]))
GROUP BY tbl_Violations![Violator's Last Name],
tbl_Violations![Violator's First Name]
HAVING (((Count(tbl_Violations.[Policy Violated]))>10));

You now have a list of the people you want the detail on. Link this query in
your main query with name or id. It will limit your results to the desired
individuals.