By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

Crosstab Queries: Show rows with no values in columns

P: 2
I have a crosstab query of events by month, and many months do not have any events. How can I make the crosstab show the months (crosstab rows) that don't have any events? Is there a shortcut? Here is the code:

TRANSFORM Val(nz(Count(Event),0)) AS CountOfEvent
SELECT Format([Date],"yyyy-mm") AS MY
FROM dbo_Table
GROUP BY Format([Date],"yyyy-mm")
PIVOT dbo_Table.Event;


Thanks for any help that anyone can give, I'm stumped! Surely there is an easy solution to this simple need.
Nov 13 '06 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,485
Not really.
You would need to create a recordset which included all the dates between the first and last found in your dataset.
With this you would need to form a query based on this new dataset with a LEFT JOIN to your old one.
This would produce rows for each date but 'empty' dates would have Null values for all other fields.
When transformed I would expect this to give you what you're after.

Never confuse 'easy for humans to understand' with 'easy for an RDBMS to process'.
They're rarely even similar.
Nov 13 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this:

TRANSFORM Val(nz(Count(Event),0)) AS CountOfEvent
SELECT dbo_Table.Event
FROM dbo_Table
GROUP BY dbo_Table.Event
PIVOT MonthName(month([Date])) IN ("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December");
Nov 13 '06 #3

P: 2
Never confuse 'easy for humans to understand' with 'easy for an RDBMS to process'.
They're rarely even similar.

Thanks, that does seem to do the trick. I was just fantasizing that I could have some easy command like "show all". :-)
Nov 13 '06 #4

P: 1
I am also trying to do something similiar, but am not really familiar with cross tab reports. I ran the Wizard, but I have an extra field in my query that I don't want.

Basically, I need to run a query of a listing of policies with review dates (which are actually review years). I need these years listed in columns, so my query should ultimately look like this:

Policy Name 2004 2005 2006
A x
B x
C x

I had someone tell me this was a cross tab query. This sql statement is below. One of my problems is that I have a column in my results under the heading of <>. Why is this? Also, what is the Max IIf statement?

Thanks,
Dawn



TRANSFORM Max(IIf([Year Policy Reviewed]>0,"X","")) AS [Select]
SELECT qryLocalPolicyReviewforReport.[Policy Name], qryLocalPolicyReviewforReport.[Policy Category], qryLocalPolicyReviewforReport.[Policy Type], qryLocalPolicyReviewforReport.[Policy Description]
FROM qryLocalPolicyReviewforReport
GROUP BY qryLocalPolicyReviewforReport.[Policy Name], qryLocalPolicyReviewforReport.[Policy Category], qryLocalPolicyReviewforReport.[Policy Type], qryLocalPolicyReviewforReport.[Policy Description]
PIVOT qryLocalPolicyReviewforReport.[Year Policy Reviewed];
Jan 3 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.