Help with Crosstab Query

[E.C.]

Crosstab Query Help

I am trying to create a crosstab query to compare sales by customer-
then by year & month. I want it to be set up the following way:

January February March .....
Customer A 2005 20 0 0
Customer A 2006 0 0 0
Customer A 2007 30 0 20
Etc...

I am having trouble with displaying the Year data if the customer did
not buy anything that year- I want that data to be displayed as zeros
as shown above.

With what I have so far this is what I am getting: (It is just
omitting the year) (2006 in this example)

January February March .....
Customer A 2005 20 0 0
Customer A 2007 30 0 20

Below is my SQL:

TRANSFORM Val(nz(Sum([O_SalesAmount]),0)) AS Expr2
SELECT Orders.[O_Customer Number], Year([O_OrdDate]) AS [Year]
FROM Orders
GROUP BY Orders.[O_Customer Number], Year([O_OrdDate])
ORDER BY Orders.[O_Customer Number], Year([O_OrdDate])
PIVOT Format([O_OrdDate],"mmmm") In
(January,February,March,April,May,June,July,August,September,October,November,December);


Does anyone have any suggestions?

 

[Guest]

I would create a query of every unique customer and year. Then join this to
your crosstab with joins that include every record from the customer/year
query. You can use Nz() to convert the nulls to zeros.