Converting Null Values to Zero Using a Crosstab Query

[instructorgirl]

I have four ratings in a table, Excellent, Good, Fair and Poor. Sometimes for a given month, there won't be surveys for all four ratings, so I would like to display zeros in those ratings for that given month. Below is the current SQL code I am using in my query. Following the code is a sampling of the data. Instead of showing just the ratings for Excellent and Fair, I would also like to see Good and Poor showing zeros.

TRANSFORM Count(qryRateOverPlantCase.txtSessionID) AS CountOftxtSessionID
SELECT tblRatingValues.Rating, Count(qryRateOverPlantCase.txtSessionID) AS Total
FROM tblRatingValues INNER JOIN qryRateOverPlantCase ON tblRatingValues.Rating = qryRateOverPlantCase.txtOverall
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");

CURRENT RESULT:
Rating Total Jan Feb
Excellent 2 1 1
Fair 2 1 1



WHAT I WANT TO SEE:
Rating Total Jan Feb
Excellent 2 1 1
Good 0 0 0
Fair 2 1 1
Poor 0 0 0

I would truly appreciate any help I can get. I have run out of ideas!!!!! Thanks!

 

[PHV]

Have you tried to replace INNER JOIN with LEFT JOIN ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[instructorgirl]

I changed INNER JOIN to LEFT JOIN and it supplied zeros in my TOTAL column but not for the individual months. Any other ideas?

 

[Knicks]

I'm not sure if this totally applies but I have forced zeros using

CLng(NZ([Febuary],0))

 

[instructorgirl]

Where would I include this? In my SQL statement or in my query in the field? The field I am trying to count is a text field called txtSessionID. So would it look like this: CLng(NZ([txtSessionID],0))

 

[dhookom]

TRANSFORM Val(Nz(Count(qryRateOverPlantCase.txtSessionID),0)) AS CountOftxtSessionID


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]