MS Access SQL Count() Aggregate function

  • Thread starter Thread starter David Maxfield
  • Start date Start date
D

David Maxfield

I'm having problems with a query that is counting the number of staff that are associated with a particular grade.
If they have more than one claim then the Pay Reference number is more than just the one.
E.g. ClaimId PayRef# StaffGrade
1 123 A1
2 123 A1
3 123 A1

The result should just be 1 staff member at grade A1 but instead the the result gives 3 staff members.

I have tried to use the SQL

Select Distinct Count(PayRef#),StaffGrade
From......................
Group by StaffGrade,PayRef#

In paradox it is possible to use

Select Count(Distinct PayRef#)

but this aggregate call is not valid in Access97.
Any thoughts?????????????

Regards
David
 
Access doesn't recognize the Distinct in conjunction with Count.

What you need to do is create an intermediary query:

SELECT DISTINCT PayRef#, StaffGrade
FROM......................

and save it. (For the sake of discussion, let's assume you save it as
qryPayRefStaffGrade)

Now, create a second query:

SELECT COUNT(PayRef#), StaffGrade
FROM qryPayRefStaffGrade
GROUP BY StaffGrade

(Note that you do not include the field being counted in the GROUP BY)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


David Maxfield said:
I'm having problems with a query that is counting the number of staff that
Click to expand...
are associated with a particular grade.
 
Back
Top