SQL crosstab query in Access

Networking/Security Forums -> Databases

Author: jf0027 PostPosted: 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?


Networking/Security Forums -> Databases

output generated using printer-friendly topic mod, All times are GMT + 2 Hours

Page 1 of 1

Powered by phpBB 2.0.x © 2001 phpBB Group