Crosstab Queries: Show rows with no values in columns

[jeanlee]

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.

[NeoPa]

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.

[MMcCarthy]

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");

[jeanlee]

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". :-)

[dmfurlong]

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];