• Twitter
  • FaceBook

Security Forums

Log in

FAQ | Usergroups | Profile | Register | RSS | Posting Guidelines | Recent Posts

SQL crosstab query in Access

Users browsing this topic:0 Security Fans, 0 Stealth Security Fans
Registered Security Fans: None
Post new topic   Reply to topic   Printer-friendly version    Networking/Security Forums Index -> Databases

View previous topic :: View next topic  
Author Message
Just Arrived
Just Arrived

Joined: 07 Jul 2005
Posts: 0


PostPosted: Thu Dec 07, 2006 4:29 pm    Post subject: SQL crosstab query in Access Reply with quote

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?

Back to top
View user's profile Send private message
Display posts from previous:   

Post new topic   Reply to topic   Printer-friendly version    Networking/Security Forums Index -> Databases All times are GMT + 2 Hours
Page 1 of 1

Jump to:  
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

Looking for more Windows Networking info?

Sign up to the WindowsNetworking.com Monthly Newsletter, written by Enterprise Security MVP Deb Shinder, containing news, the hottest tips, Networking links of the month and much more. Subscribe today and don't miss a thing!
View a sample newsletter.

Become a WindowsNetworking.com member!

Discuss your Windows Networking issues with thousands of other Windows Newtorking experts. Click here to join!

Community Area

Log in | Register

Readers' Choice

Which is your preferred data recovery solution?

Follow TechGenix on Twitter