Monthly Totals

[21Qwerty21]

Hi,
I have a database which is used to record comments received.
I need to produce a query / report that shows a monthly total.
I.E. we received 5 comments in Jan, 8 in Feb etc.

I have a [No:] field which is the Primary key and assigns each comment an ID and a [Date Received] field (among others)

I have tried:-

CountC: Count(Month([Date Received]))

MonthC: Format$([Date Received],"mmm yy")

Date Received (sorted ascending)

This does work after a fasion but if I get 3 dates recieved in 1 month I get 3 rows once the query is run.
If 2 or more comments are received in one day then they are counted correctly

CountC MonthC
1 Feb 04
1 Feb 04
1 Apr 04
1 May 04
1 Jun 04
1 Jun 04
1 Jun 04
1 Jul 04
1 Jul 04
2 Jul 04
1 Jul 04
1 Jul 08

Thanks
Qwerty

 

[PHV]

MonthC must be grouped.
Have you tried this ?
CountC: Count(*)

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

 

[JonFer]

It looks like you have GROUP BY set on "Date Received" which you probably have to sort it correctly. Use this instead of Date Received for sorting:

Format([Date Received],"yyyymm")

You can also use this to replace your MonthC expression and then just sort on MonthC.