F
FNG
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
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