How to group by month

[okrobie]

Hello, I have a date field in my database and I would like to group the dates by month in a query. Can someone help me do it?

[ConneXionLost]

Build a calculated field in your query to display the month number like this:

RefMonth: Month([your date field])

then group your query on RefMonth.

Note ~ months in different years will group together with this. If this is a problem, then you'll need to do the same thing with YEAR()

Cheers,

[okrobie]

Thank you ConneXionLost Mark me solved it works great. Regards

[ConneXionLost]

Happy to help, but you'll need to mark it solved yourself. That must be done by the OP (Original Poster).

Cheers,

[l3111]

Hi
Can you help me develop this solution? I have a list of referrals, some on the same day, and i need to group them month and year. I have created a query which uses the formulas you have mentioned above (to show the month and the year) however, i have 19 entries, but only 15 are being counted.
I then need to concatenate the month and year fields, and for this new field to be shown as March 2011, rather than the default 03-2011 that access insists upon.

thennnnnn i would like to count this field to allow me to create a report that shows the following

Month Referrals
March 2011 5
April 2011 3
May 2011 6
June 2011 5
etc

Can you help??

l3111, Manchester, UK