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

 

[MGFoster]

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?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Create a column in the SELECT clause like this:

SELECT Month(date_column_name) AS TheMonth, ...

Put it in the GROUP BY clause also, this will also sort by the month:

GROUP BY Month(date_column_name), ...

If sorting Descending, use an ORDER BY clause like this:

ORDER BY Month(date_column_name) DESC ... etc.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSWUywIechKqOuFEgEQIqFQCg1NdR/QiemCVIQCT3tSRIhDMNHjgAoKkx
RDjAmWXySQfjvSNjjqXxqQ5W
=C6Mi
-----END PGP SIGNATURE-----

 

[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