sql statement to sum results over months and years

[bai73]

I have date wise results in a database and would like to sort them out into months and years and also over the respective year in a table form. I would appreciate the help in writing a sql statement for a Access database. Thanks!

 

[PHV]

What have you tried so far and where in your SQL code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bai73]

Hello PVH,
I would try to write in detail about my problem..I have a date wise sales data for the last four years..I want to get a report where months are the columns and years as the rows like below:

[Year]Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
2004 ? ? ? ? ? ? ? ? ? ? ? ? ?
2005 ? ? ? ? ? ? ? ? ? ? ? ? ?
2006
2007

The columns in the access data base are:

ID vDate Sales

I need help in writing a sql query for getting this report at our site developed with frontpage 2002 using visual basic..I hope I have explained every thing and would love the answer.
Thanks
bai73

 

[PHV]

You may try a crosstab query:

TRANSFORM Sum(Sales) AS colValue
SELECT Year(vDate) AS [Year], Sum(Sales) AS Total
FROM yourTable
GROUP BY Year(vDate)
PIVOT Format(vDate,'mmm') IN ('Jan','Feb','Mar',...,'Nov','Dec')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bai73]

Hello PHV,
I have tried this code and no error message is coming. Since I am new to this Transform type of sql statement, am unable to understand why any results are not being retrieved from the Access database which has 5 years of entries in it. Do this statement further needs to set any record sets for retrieving data. I hope you would spare some time by explaining how to retrieve results using this statement in the table form already explained above.
Thanks!