Query with calculated field (count) question

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

allie357

I have a query I am trying to construct to run a report that will show
violators with over x violations after choosing a particular
violations. Now the violations are a text field that is counted. I
know you can't sum a calculated field. Can anyone give me any
suggestions?

Thanks!
 
Something like this should do it. Substitute your table and field names, of
course.

SELECT Violations.Violator, Count(Violations.Violation) AS CountOfViolation
FROM Violations
WHERE (((Violations.Violation)=[Which Violation?]))
GROUP BY Violations.Violator
HAVING (((Count(Violations.Violation))>=[How Many?]));
 
If you just need the count in the reports, then you also may try to add to
your report: =count([primary key field]) on each footer for each group. If
you are not using the [primary key field] just count any field that is part
of your report.

Brendan Reynolds said:
Something like this should do it. Substitute your table and field names, of
course.

SELECT Violations.Violator, Count(Violations.Violation) AS CountOfViolation
FROM Violations
WHERE (((Violations.Violation)=[Which Violation?]))
GROUP BY Violations.Violator
HAVING (((Count(Violations.Violation))>=[How Many?]));

--
Brendan Reynolds
Access MVP

allie357 said:
I have a query I am trying to construct to run a report that will show
violators with over x violations after choosing a particular
violations. Now the violations are a text field that is counted. I
know you can't sum a calculated field. Can anyone give me any
suggestions?

Thanks!
Click to expand...
Click to expand...
 
Yes, you're quite right. In the report you could group by, e.g. 'Violator'
and again by 'Violation', and include a text box in the 'Violation' group
footer with a control source such as '=Count(ViolationID)' or '=Count(*)'.

--
Brendan Reynolds
Access MVP

Ricoy-Chicago said:
If you just need the count in the reports, then you also may try to add to
your report: =count([primary key field]) on each footer for each group.
If
you are not using the [primary key field] just count any field that is
part
of your report.

Brendan Reynolds said:
Something like this should do it. Substitute your table and field names,
of
course.

SELECT Violations.Violator, Count(Violations.Violation) AS
CountOfViolation
FROM Violations
WHERE (((Violations.Violation)=[Which Violation?]))
GROUP BY Violations.Violator
HAVING (((Count(Violations.Violation))>=[How Many?]));

--
Brendan Reynolds
Access MVP

allie357 said:
I have a query I am trying to construct to run a report that will show
violators with over x violations after choosing a particular
violations. Now the violations are a text field that is counted. I
know you can't sum a calculated field. Can anyone give me any
suggestions?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top