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

 

[khawar]

Try this

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

 

[GordonCopestake]

Hi Khawar,
thanks for your swift reply.

When I try your code I get the error:

You tried to execure a query that does not include the specified expression 'Format([RaisedDate],"yyyy")' as part of an aggregate function.

 

[DCrake]

Design your original crosstab query then bring down the date field you want to extract the year from.

Place this in the first column and code as follows

ActYear:Format([DateField],"yyyy")

Make this a row item
Group by this field as well
Sort Ascending