Help with Crosstab Query



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

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

Does anyone have any suggestions?




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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question