Help with Crosstab Query


E

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?
 
Ad

Advertisements

G

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.
 

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

Top