Number of items per Month query

[rt52]

Using Access97.
The data is in a table with date and type of intervention fields. I need is count of type of interventions for each month on a dynamic report - eg the last 12 months.

tbintervention
date type
1/1/04 ABX
1/1/04 PNE
1/2/04 ABX
2/1/04 ABX
3/1/04 CLA
3/12/04 DOSH
3/12/04 THER
running the following query:

SELECT DISTINCT tbIntervention.date, Count(tbIntervention.type) AS CountOftype
FROM tbIntervention
GROUP BY tbIntervention.date
HAVING (((tbIntervention.date) Between #1/1/2004# And #1/31/2004#))
ORDER BY tbIntervention.date;

gives:
date countoftype
1/1/04 2
1/2/04 1
2/1/04 1
3/1/04 1
3/12/04 2
then running the following query:

SELECT Sum(qryMonthByDay.CountOftype) AS Total
FROM qryMonthByDay;

results with a value of 3 (using the dates above).

What I need is select statement which will produce a month and number of interventions which may then be charted:

Month #interv
Jan2004 3
Feb2004 1
Mar2004 3
etc...

Any suggestions? Thank you.

 

[PHV]

Something like this ?
SELECT Format([date], "mmmyyyy") AS [Month], Count(*) AS [#interv]
FROM tbIntervention
WHERE [date] Between #1/1/2004# And #12/31/2004#
GROUP BY Format([date], "mmmyyyy");

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rt52]

Pretty much does it. Just a little manipulation
with the ordering.
Thanks.

 

[wvmbark]

Instead of "Format([date], "mmmyyyy")", I use "DateSerial(Year([Date]), Month([Date]),1)"

This keeps the field as a date type. It converts each date in your table to the first day of its respective month. You can then group by on that field. Works great and sorts properly.

Mike