How to include zero-count results in query

Viewed 8616

I have these two tables:

Operators:

Id Nome
--+----
 1 JDOE
 2 RROE
 3 MMOE

Calls:

Id CallDate OpId
--+--------+----
 1 20161228    2
 2 20161228    3
 3 20161228    2
 4 20161228    3
 5 20170104    1
 6 20170104    2
 7 20170104    1    

And this query:

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON Operators.id = Calls.OpId
GROUP BY Calls.CallDate, Operators.id, Operators.Nome
HAVING Calls.CallDate=20170104;

Which returns:

Id Nome CountCalls
--+----+----------
 1 JDOE          2
 2 RROE          1

How can I make it return this, instead?

Id Nome CountCalls
--+----+----------
 1 JDOE          2
 2 RROE          1
 3 MMOE          0

That is, how to include in any query also the zero results from main table which has no occurrence in left joined table, at least in the data slice defined by the query filtering criteria?

This is Access 2013.

I've read this answer but couldn't see how it is different from what I'm doing.

2 Answers
Related