CODEWITHSUNDEEP
sqlms-access
Dempers
Dempers

Reputation: 13

Forcing a '0' return value when no matching records exist

Background:

I have a table in an Access database (let's call it [tblMyTable]). The table has thousands of entries for each month, with each entry belonging to a category, e.g:

ID  Month  Category
--  -----  --------
 1      1  White
 2      1  Black
 3      2  White
...etc.

Problem:

When I run my (somewhat simplified) query 

SELECT tblMyTable.Month, COUNT(tblMyTable.ID) AS CountedIDs 
FROM tblMyTable 
WHERE tblMyTable.Category = "Black" 
GROUP BY tblMyTable.Month

the return is:

Month  Count
-----  -----
    1      1

whereas what I expect/need is:

Month  Count
-----  -----
    1      1
    2      0

I've tried using the NZ() function to force a '0' return, but not having any luck with that either, hence reaching out to everyone here...

If anything is missing or looks weird with the above, please shout out and I'll try to address it.

Upvotes: 1

Views: 284

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

Create a table named [MonthNumbers] like so

MonthNumber
-----------
          1
          2
          3
          4
          5
          6
          7
          8
          9
         10
         11
         12

and then use the following query (which includes your original query as a subquery):

SELECT 
    mn.MonthNumber AS Month,
    Nz(gb.CountedIDs, 0) AS CountedIDs
FROM
    MonthNumbers mn
    LEFT JOIN
    (
        SELECT 
            tblMyTable.Month, 
            COUNT(tblMyTable.ID) AS CountedIDs 
        FROM tblMyTable 
        WHERE tblMyTable.Category = "Black" 
        GROUP BY tblMyTable.Month
    ) gb
        ON gb.Month = mn.MonthNumber

It returns

Month  CountedIDs
-----  ----------
    1  1         
    2  0         
    3  0         
    4  0         
    5  0         
    6  0         
    7  0         
    8  0         
    9  0         
   10  0         
   11  0         
   12  0

Upvotes: 1

Related Questions