Return Zero Instead of Null from a Query

[Guest]

I am in over my head. I have a query that is "group by" on two fields and
then "count" on a third with a "where" statement behind the count. I am
trying to run a query on several of these queries and if any one returns a
null value the whole thing turns to null.

For example, basically my query look like this: Huge table of data. I group
on field called "color" which is populated with either "red" or "blue. Next
field is also grouped on and called "level", it's either populated with
"high" or "low". Next field is called "temp" and it's populated with a number
somewhere between negative 300 and positive 2000, this field is a "count"
field. Last field is "temp" again but this time it's a "where" statement with
a criteria of <90. Then I create new qeries the same way for >= 90 and for
<240 and >=240.

Occasionally, there are zero records that meet the blue, high, <90 criteria.
So, that query returns a null value.

When I create a query of the queries using the different "where" staments,
if there is one null value, the whole thing (and I mean the WHOLE thing)
returns null values.
Also, these are all "make table" queries so it is not something I can
address in the table itself, it has to be in the query because each time it
runs it deletes the old table.

I've tried putting IIF and NZ statements in the criteria field of the count
column trying to change null to zero and I must be doing something wrong.

I would really appreciate any help especially the expression I need for the
criteria to force a zero instead of null (or the name and addres of the SOB
who did not make null = 0 a working piece of code!)

Thanks in advance

 

[Tom Ellison]

Dear FNG:

Your difficulty could be addressed directly and with less explanation on
your part if you would please post the SQL of your query here. Likely,
someone could modify it and propose a solution, which you could then very
easily try.

Tom Ellison

 

[Guest]

Maybe this will do what you want. You might want to change the labels from
Cool, Warm, and Hot to something else.

TRANSFORM Count([Troy_s Table2].Temp) AS CountOfTemp
SELECT [Troy_s Table2].Color, [Troy_s Table2].Level
FROM [Troy_s Table2]
GROUP BY [Troy_s Table2].Color, [Troy_s Table2].Level
PIVOT IIf([Temp]<90,"Cool",IIf([Temp] Between 90 And 239.999,"Warm","Hot"));