Grouping by Year then by Months

[juintade]

I have been assigned a task to create a query that gets record from Access database by grouping it by year and then by months.


DATA ILLUSTRATION

date incidents
01/01/2007 2
01/02/2007 3
01/03/2007 4
01/01/2008 1
20/01/2008 1
01/02/2008 1

Based on the above data illustration, what I am trying to do is to get total by year and then break it down by the months

year 2007 total_incident: 9
Jan total_incident: 2
feb total_incident:3
mar total_incident: 4

year 2008 total_incident:3
jan total_incident:2
Feb total_incident: 1


I am using Microsoft Access, but trying to get the result into a web form. Many thanks in advance

 

[roelandkim]

One way to get this is to break the query down into several different queries, start with this one. Basically it will convert the dates into month/years, replace "table1" with whatever your table name is:

Qry_DateConversion

SELECT Table1.date, Format([date],"yyyy") AS dateyear, Format([date],"mmmm") AS datemonthyear, Table1.incidents
FROM Table1;

Once you have that done you can then query the subquery using the Totals function in access to get a summary by year:

SELECT Qry_DateConversion.dateyear, Sum(Qry_DateConversion.incidents) AS SumOfincidents
FROM Qry_DateConversion
GROUP BY Qry_DateConversion.dateyear;


The same thing can be done on the monthly breakdown, but since you want it broken down by year you would need to add additional criteria:


SELECT Qry_DateConversion.datemonthyear, Sum(Qry_DateConversion.incidents) AS SumOfincidents
FROM Qry_DateConversion
WHERE (((Qry_DateConversion.date) Between #1/1/2007# And #12/31/2007#))
GROUP BY Qry_DateConversion.datemonthyear;

Hope this helps.

 

[DCrake]

Simple Software Solutions

Hi

Why not try a crosstab query whereby your incident types are your row headings and your dates are displayed in the columns as Month(Incident Date) with a YTD total at the end. And count the incidents as your column data.

Vis:

Incident Apr May June ... YTD
Descript 4 6 0 10
Incident 2 Apr May June ... YTD
etc...

An extra tip if you try this is to set the column headings in your crosstab query as static headings Apr,May,Jun, Jul,etc

This ensures that any months that do not have any data will still appear in the query.

Code Master: