Using the Nz function in a query

[Guest]

I am trying to make a query to show a count of how many subjects have Moved
(True/False), grouped by School. If no subjects in a certain school have
moved, I’d like the query to return a 0 instead of simply omitting the group.

For example:

3 ABC School
0 XYZ School
1 JTH School

I’m using the Nz function in my query, but instead of showing a 0 for null
groups, it’s omitting the group (just as if I didn’t use the Nz function).

Any ideas? SQL Below.

Stacey

SELECT Count(Nz([Moved],0)) AS MovedNz, ctblSchools.School
FROM ctblSchools INNER JOIN tblParents ON ctblSchools.SchoolPrefix =
tblParents.SchoolPrefix
WHERE (((tblParents.Moved)=True))
GROUP BY ctblSchools.School;

 

[Ken Snell [MVP]]

Your query won't return any records that don't exist (you're using an INNER
JOIN, plus you tell the query to omit any "many-side" records that don't
have a value of True for the Moved field, which means Null records are
omitted too), so let's try a different approach:

SELECT (SELECT Count(*) FROM tblParents
WHERE tblParents.SchoolPrefix = ctblSchools.SchoolPrefix
AND tblParents.Moved=True) AS MovedNz,
ctblSchools.School
FROM ctblSchools
GROUP BY ctblSchools.School;