Posted: Thu Dec 07, 2006 4:29 pm Post subject: SQL crosstab query in Access
Hi, I'm doing a crosstab query in Access so that i can do a graph with the data i have. What it does is that i count the number of customer for a month period, and i compare them by year. The problem is, if i do not have any customer for a particular month, the query doesn't pick up this month and my graph will only show 11 months.
Here's my query. The where clause is long because i select the highest year in the table and go back 4 years max for the graph.
TRANSFORM Count([Customer].C_ID) AS CountOfC_ID
SELECT Format([C_Date],"mm") AS Expr1
FROM [Customer]
WHERE (((Format([C_Date],"yyyy")) In (SELECT DISTINCT Format([Customer].C_Date,"yyyy") FROM [Customer] WHERE Format([Customer].C_Date,"yyyy") <=(SELECT DISTINCT Format(Max([Customer].C_Date),"yyyy") FROM [Customer];) AND Format([Customer].C_Date,"yyyy") >=(SELECT DISTINCT Format(Max([Customer].C_Date),"yyyy")-4 FROM [Customer];); )))
GROUP BY Format([C_Date],"mm")
PIVOT Format([C_Date],"yyyy");
Any Idea how i can force to pick up the month i don't have any data?
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum