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