Forcing Column Headings with Null Values Not to Display in Crosstab

[techgirl10]

I am trying to show a rolling 18 months in my report. This will change monthly. I have two crosstab queries created for 2006 and 2007. I have them joined together in another query. There will always be 6 months without values between those two queries. Is there in any way in a crosstab query to have months that do not have values not display? I have tried everything I can think of and have not had any luck. Can this be accomplished in SQL? I have copied part of the query to give an example of what I'm trying to do. Any help would be appreciated. Thanks!

Rating Value Total Jan Feb Mar Apr May
Excel 4 1272 161 151
Good 3 1560 142 168
Fair 2 138 19 15
Poor 1 26 1 7

 

[dhookom]

What is your SQL?

Normally columns that would not return any values do not display. We get more people coming here to ask how to make columns display that don't have data.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[techgirl10]

Here's my SQL:

TRANSFORM Count(CLng(NZ([txtSessionID],0))) AS txtSessionIDNew
SELECT tblRatingValues.Rating, tblRatingValues.Value, Count(qryRateOverPlantCase.txtSessionID) AS Total
FROM tblRatingValues LEFT JOIN qryRateOverPlantCase ON tblRatingValues.Rating = qryRateOverPlantCase.txtOverall
WHERE (((Format([txtSubmitTime],"yyyy"))=2006))
GROUP BY tblRatingValues.Rating, tblRatingValues.Value
ORDER BY tblRatingValues.Value DESC
PIVOT Format([txtSubmitTime],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[PHV]

Simply remove the In clause in the PIVOT line.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[techgirl10]

That worked fabulous! Now, how do you keep the Months from displaying in alphabetical order instead of chronological order?

 

[dhookom]

Why would the order of the fields in the crosstab make a difference if the query will be displayed in a report?

You might want to check out the complete monthly crosstab report method at faq703-5466.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]