Sort query by date

[Meejung]

I have data which as a date/time stamp. I want to create a query which give
me counts by by month by for several different categories by region. I have
created the query to give me the counts by region and by category but I can't
get it to give it to me as a monthly roll-up. Can I do all of this running 1
report or do I have to run a different report for each month?

Thanks,
Shelli

 

[John Spencer]

Use an expression like

Format([DateField],"yyyy-mm")

Group or order your data.

SELECT Format([DateField],"yyyy-mm") as theMonth
, Region
, Count(SomeOtherField) as CountSomething
FROM SomeTable
GROUP BY Format([DateField],"yyyy-mm")
, Region

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Ron2006]

John's solution is more precise than MGFoster's IF you are running the
query for more than a year.

If you run it for more than a year and use MG's approach you will NEED
to add a group for year(date_column_name) which you may want to do if
you are running a report and want a year or yeartodate total.

Ron