How to include NULL values in Group Count

Experts,

How can I create a Query that groups on a field and counts NULL values as well.

Tier 2     62
Tier 3     15

That's all. However there are 134 records. I would like the query to list:
Tier 2     62
Tier 3     15
Blank      57

I tried linking to a query with:

SELECT Count(*) AS NoTier
FROM tblStudent
WHERE (((tblStudent.tblStdTier) Is Null));

But because I am grouping my results are

Tier 2     62     57
Tier 3     15     57

I really need:
Tier 2     62
Tier 3     15
Blank      57

Because I am displaying the results in a continous form on a statistics page.

Thanks!



SELECT tblStudent.tblStdTier AS Tier, Count(tblStudent.tblStdTier) AS [Count]
FROM tblStudent
GROUP BY tblStudent.tblStdTier
HAVING (((tblStudent.tblStdTier)<>"0"));

Open in new window

shogun5Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
or this

SELECT iif([tblStdTier] is null, "NoTier",[tblstdtier]) AS Tier, Count(*) AS [Count]
FROM tblStudent
GROUP BY iif([tblStdTier] is null, "NoTier",[tblstdtier])
HAVING (((iif([tblStdTier] is null, "NoTier",[tblstdtier]))<>"0"));
0
 
Rey Obrero (Capricorn1)Commented:

try this


SELECT iif([tblStdTier] is null, "NoTier",[tblstdtier]) AS Tier, Count(ID) AS [Count]
FROM tblStudent
GROUP BY iif([tblStdTier] is null, "NoTier",[tblstdtier])
HAVING (((iif([tblStdTier] is null, "NoTier",[tblstdtier]))<>"0"));
0
 
shogun5Author Commented:
Yep! That worked! Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.