Aggregate query

[Guest]

For some reason, aggregate queries have not been my cup of tea. In the following query, all I want is to see how many (count) of each different ApptCodeID was done in the time span specified. When I try to run this query, it complains that ApptDate and ApptTypeID are not included in the aggregate function. When I put them in the aggregate, I don't get the count I am looking for. The Access help file wasn't much help and I know this is simple. Any help is appreciated!

SELECT Appointment.ApptCodeID, Count(Appointment.RecordID) AS CountOfRecordID, ApptCode.Descriptio
FROM Appointment INNER JOIN ApptCode ON Appointment.ApptCodeID = ApptCode.ApptCodeI
GROUP BY Appointment.ApptCodeI
HAVING (((Appointment.ApptDate) Between #1/1/2003# And #12/31/2003#) AND ((Appointment.ApptTypeID)=2))

 

[Roger Carlson]

Since you don't say what you are looking for, it's kinda difficult to say.
However, instead of the HAVING, try a WHERE clause:

SELECT Appointment.ApptCodeID, Count(Appointment.RecordID) AS
CountOfRecordID, ApptCode.Description
FROM Appointment INNER JOIN ApptCode ON Appointment.ApptCodeID =
ApptCode.ApptCodeID
WHERE Appointment.ApptDate Between #1/1/2003# And #12/31/2003# AND
ApptTypeID=2
GROUP BY Appointment.ApptCodeID;

The WHERE clause applies the restriction BEFORE they are grouped. The
HAVING applies the restriction AFTER they are grouped. Sometimes it makes
no difference, but other times it makes a big difference. I can't say for
sure if it will in this case or not.
--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

For some reason, aggregate queries have not been my cup of tea. In the

following query, all I want is to see how many (count) of each different
ApptCodeID was done in the time span specified. When I try to run this
query, it complains that ApptDate and ApptTypeID are not included in the
aggregate function. When I put them in the aggregate, I don't get the count
I am looking for. The Access help file wasn't much help and I know this is
simple. Any help is appreciated!!

SELECT Appointment.ApptCodeID, Count(Appointment.RecordID) AS

CountOfRecordID, ApptCode.Description