Summing a count field please help

  • Thread starter Thread starter allie357
  • Start date Start date
A

allie357

Currently I have a query that counts a text field called Policy

I need a query that sums this count field and evaluates it.

Say if the person has 3 violations they would be included in the query.
Any help is appreciated.

Here is the original query with the count:

PARAMETERS [Enter Start Date (mm/dd/yyyy)] DateTime, [Enter End Date
(mm/dd/yyyy)] DateTime;
SELECT tbl_Violations![Violator's Last Name] & ", " &
tbl_Violations![Violator's First Name] AS [Violator's Name],
tbl_Violations.[Policy Violated], Count(tbl_Violations.[Policy
Violated]) AS [CountOfPolicy Violated]
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], tbl_Violations.[Policy
Violated], tbl_Violations.[Violator's Last Name],
tbl_Violations.[Violator's First Name];
 
I'm unclear as to what you want.
Do you want anyone that has violated any one policy 3 or more times,
Or do you want any one that has three or more violations of policies
(multiples of one policy count against the total) ,
Or do you want anyone that has violated three or more policies (one
violation per policy can be counted)?

If you want to get persons that have violated any one policy three or more
times you just need to add a having clause to your query.

PARAMETERS [Enter Start Date (mm/dd/yyyy)] DateTime
, [Enter End Date (mm/dd/yyyy)] DateTime;
SELECT tbl_Violations![Violator's Last Name] & ", " &
tbl_Violations![Violator's First Name] AS [Violator's Name],
tbl_Violations.[Policy Violated]
, Count(tbl_Violations.[Policy Violated]) AS [CountOfPolicy Violated]
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]
, tbl_Violations.[Policy Violated]
, tbl_Violations.[Violator's Last Name]
, tbl_Violations.[Violator's First Name]
HAVING Count(tbl_Violations.[Policy Violated) >= 3

The remaining two can be done, but are more complex.
 
Back
Top