Aggregate query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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))
 
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

smk2 said:
For some reason, aggregate queries have not been my cup of tea. In the
Click to expand...
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
Click to expand...
CountOfRecordID, ApptCode.Description
 
Back
Top