Count Query w/o ignoring Nulls

[Dulanic]

Is there any way to do this? Right now I have something like so....

SELECT mgr_name, Count(*) AS COHCnt
FROM tblAudits
WHERE xyz....
GROUP BY  mgr_name

The problem is, it ignores 0's if there was no results from the where conditions... I need it to basically provide a list of the mgr_name and then if there was 0 results for said manager it provides a 0. I had ready that before I could do a Group by all, but I guess that is older and no longer works? Not sure, it gave an error message.

Right now it would show for example...

Mgr1 5
Mgr2 3
Mgr4 2

I need it to show....

Mgr1 5
Mgr2 3
Mgr3 0
Mgr4 2

[jzwp11]

You will need a new query that brings in all manager names and left join that to your counting query. You will also need an IIF(IsNull()) nested function to force zero when no applicable count exists for a manager. The query would look something like this (you will have to substitute your actual field/table/query names):

SELECT tblManagers.mgr_name, IIF( IsNull(CountingQuery.COHCnt), 0 CountingQuery.COHCnt) as AuditCount
FROM tblMangers LEFT JOIN CountingQuery ON tblManagers.mgr_name=CountingQuery.mgr_name

[Dulanic]

Thanks much! I made a few tweaks and was able to get that to work. Appreciate the help.

[jzwp11]

You're welcome. Glad you were able to get to a solution!