SQL group/count help

[scottb]

This is probably simple but I continue to struggle with these:

Exists a table (demographics). Two fields I'm interested in: procedure and date_of_surgery. I am trying to create two queries that will show me the top 10 precedures by month and the average of the top 10 procedures per month. I have started:
SELECT TOP 10 DEMOGRAPHIC.PROCEDURE, Count(DEMOGRAPHIC.[DATE_OF _SURGERY]) AS Jan
FROM DEMOGRAPHIC
GROUP BY DEMOGRAPHIC.PROCEDURE
HAVING (((Count(DEMOGRAPHIC.[DATE_OF _SURGERY])) Between #1/1/2010# And #1/31/2010#))
ORDER BY DEMOGRAPHIC.PROCEDURE DESC;

which is already resulting in nothing.
Any help woul be appreciated. Thank you.
-Scott

[Becks]

The HAVING line is trying to use a number (Count) between two dates. Try replacing with
HAVING ((DEMOGRAPHIC.[DATE_OF _SURGERY]) Between #1/1/2010# And #1/31/2010#)

My SQL is a bit rusty - my initial thought was that you should be using a WHERE statement

Regards
Kevin

[HansV]

In the first place: I'd rename the date field from DATE_OF _SURGERY (with a space after OF) to DATE_OF_SURGERY (without spaces), that makes it easier to work with the field. But that is not essential. In the SQL below I have used the field name without spaces.

I'd do this in two steps:

1) A query qryCount to count the number of records by month and by procedure:

SELECT [DATE_OF_SURGERY]-Day([DATE_OF_SURGERY])+1 AS Mnth, DEMOGRAPHIC.PROCEDURE, Count(DEMOGRAPHIC.ID) AS N
FROM DEMOGRAPHIC
GROUP BY [DATE_OF_SURGERY]-Day([DATE_OF_SURGERY])+1, DEMOGRAPHIC.PROCEDURE

2a) A query qryTop based on qryCount to return the top 10 procedures per month:

SELECT qryCount.Mnth, qryCount.PROCEDURE, qryCount.N
FROM qryCount
WHERE qryCount.PROCEDURE In (SELECT TOP 10 PROCEDURE FROM qryCOUNT AS Q WHERE Q.Mnth=qryCount.Mnth ORDER BY N DESC)
ORDER BY qryCount.Mnth, qryCount.N DESC

2b) A similar query qryAvg, also based on qryCount, to compute the average number of records of the top 10 procedures per month:

SELECT qryCount.Mnth, Avg(qryCount.N) AS AvgN
FROM qryCount
WHERE qryCount.PROCEDURE In (SELECT TOP 10 PROCEDURE FROM qryCOUNT AS Q WHERE Q.Mnth=qryCount.Mnth ORDER BY N DESC)
GROUP BY qryCount.Mnth

[scottb]

I have just been able to get back to this. This helped tremendously. Thank you all very much. - Scott