Account for nulls in crosstab

[Guest]

I have a crosstab query:

TRANSFORM Avg([numerator]/[denominator]) AS [Percent > 20 min]
SELECT X1.Diagnosis
FROM Z7 INNER JOIN X1 ON (Z7.Month = X1.Month) AND (Z7.Diagnosis =
X1.Diagnosis)
GROUP BY X1.Diagnosis
PIVOT X1.Month;

Is there any way to account for null values in the [Percent>20min]?

Thank you

 

[Guest]

Hi Jeff,

Can you add a criteria to filter out nulls?

WHERE denominator is not null


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

You can also use the nz function to assign a value in the event the value is
null

ie. where x is null nz(x)=0 or nz(x,1)=1.

nz can also be used for strings eg nz("emptystring","empty")

hope this helps