How do I sum data by Month/Year using a crosstab query?

[jose3106]

I have a dataset that spans multiple years. I want to create a crosstab query that sums the data by Month/Year. When I run the query, the results are based on the actual date resulting in a crosstab query that has thousands of columns. Here is the desired output:
12/05 1/06 2/06
Company X $252 $500 $100

Thanks!

 

[jrbarnett]

How far have you got so far? Can you post your SQL?

Also, what is the structure of the underlying data in your database?

John

 

[jose3106]

Below is the SQL code. It is currently formatted to sum the data by month.

TRANSFORM Sum(Bookings.[Booking Amount]) AS [SumOfBooking Amount]
SELECT Bookings.[National Account], Sum(Bookings.[Booking Amount]) AS [Total Of Booking Amount]
FROM Bookings
GROUP BY Bookings.[National Account]
PIVOT Format([Contract Commence Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[jrbarnett]

Try something like:

TRANSFORM Sum(Bookings.[Booking Amount]) AS [SumOfBooking Amount]
SELECT Bookings.[National Account], Sum(Bookings.[Booking Amount]) AS [Total Of Booking Amount]
FROM Bookings
GROUP BY Bookings.[National Account]
PIVOT Format([Contract Commence Date],"yyyy/mm");

Its not quite what you wanted, but fairly close.

John

 

[jose3106]

Thank you!!!