Using the Nz function in a query

S

smoutsat

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;
 
K

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;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

NZ function 1
Query set-up not using cross-tab 9
Nz function in query 2
Using Nz in inventory running balance 2
How to Handle Nulls in SQL? 7
Returning zero in a query? 3
Generic Query? 1
Nz - need code - 2

Top