Using Count, Group By, and MOD question

[BrockWade]

In the below query I'm experimenting with I am trying to return the City, State, Grouping by DoctorID, Number of Doctors, and only returning anything if the number of Doctors with a particular ID is an odd number:



SELECT qryUnion.[City], qryUnion.[State], qryUnion.[DoctorsID],
Count(qryUnion.DoctorsID) AS CountOfDoctorsByID,
FROM qryUnion
WHERE (CountOfDoctors MOD 2) > 0
GROUP BY qryUnion.[DoctorsID];

Any pointers on this query?... it is stating for one thing that it doesn't like it that I did not include City and State as part of an aggregate function.

[pbaldy]

Try

SELECT qryUnion.[City], qryUnion.[State], qryUnion.[DoctorsID],
Count(qryUnion.DoctorsID) AS CountOfDoctorsByID,
FROM qryUnion
GROUP BY qryUnion.[City], qryUnion.[State], qryUnion.[DoctorsID]
HAVING Count(qryUnion.DoctorsID) MOD 2 > 0

[BrockWade]

That works... thanks!

[pbaldy]

Happy to help!