Yearly Summary

[GordonCopestake]

Hi,


I am trying to convert a table of data to a yearly/monthly summary and can't seem to work out how to do it.

The data I have looks like this:

ID | date     | other info
1  | 01/01/11 | ...
2  | 04/01/11 | ...
3  | 06/01/11 | ...
etc

and I am trying to convert this to a simple count of each item per month per year, i.e.

     |Jan | Feb | March | April | May | June | July | Aug | Sept | Oct | Nov | Dec
2008 | 5  | 65  | 45    | 23    | 432 | 45   | 89   | 54  | 45   | 21  | 4   | 0
2009 | 9  | 87  | 75    | 35    | 985 | 121  | 98   | 87  | 65   | 32  | 21  | 7
2010 | 12 | 127 | 175   | 85    | 975 | 184  | 108  | 101 | 86   | 57  | 74  | 25
2011 | 56 | 331 | 321

I've tried getting this out of a crosstab but can't get it to split it into years (I can see how to do it into months per ID but not into years):

TRANSFORM Count(CCF.CCFNo) AS CountOfCCFNo
SELECT CCF.CustomerACNo
FROM CCF
GROUP BY CCF.CustomerACNo
PIVOT Format([RaisedDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

But I can't work out how to do it into years as above

Thanks for any help

Gordon

[June7]

First do a query that constructs a field for the Year part:
SELECT Table1.ID, Table1.Date, Year([Date]) AS RepYear FROM Table1;
Then build the crosstab from this query. Example of all-in-one nested query:
TRANSFORM Count(Query1.[ID]) AS CountOfID
SELECT Query1.[RepYear], Count(Query1.[ID]) AS [Total Of ID]
FROM (SELECT Table1.ID, Table1.Date, Year([Date]) AS RepYear
FROM Table1) As Query1
GROUP BY Query1.[RepYear]
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

[GordonCopestake]

June7, thats brilliant, exactly what I needed, thanks for your help