Solved

How to include NULL values in Group Count

Posted on 2010-11-16
3
694 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:shogun5
  • 2
3 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34144896

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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 34144901
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
 

Author Closing Comment

by:shogun5
ID: 34144962
Yep! That worked! Thank you!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question