Counting Groups not matching criteria

[Inquisitive1]

I am wondering if there is a way to use a query to return a group and 0
value if the group did not meet the criteria.

Below is the SQL to my query:

SELECT [tblMaster-ApprovedSourcing].[Commodity Code],
Count([tblMaster-ApprovedSourcing].CertStatus) AS CountOfCertStatus
FROM [tblMaster-ApprovedSourcing]
WHERE ((([tblMaster-ApprovedSourcing].CertStatus) Like "*sales*" Or
([tblMaster-ApprovedSourcing].CertStatus) Like "*corp*" Or
([tblMaster-ApprovedSourcing].CertStatus) Like "*dist*"))
GROUP BY [tblMaster-ApprovedSourcing].[Commodity Code];

This query returns the following:
C 1
M 1

The problem is that I am missing two Commodity Codes from the results.
I would like to show E with a 0 and Unknown with a 0 (both of these
commodity codes are in the tblMaster-ApprovedSourcing). I looked up
the Nz function by reading another post with a similar issue, but I do
not understand how to use it in a query.

Can somebody please help me?

Thanks!

 

[Marshall Barton]

I am wondering if there is a way to use a query to return a group and 0
value if the group did not meet the criteria.

Below is the SQL to my query:

SELECT [tblMaster-ApprovedSourcing].[Commodity Code],
Count([tblMaster-ApprovedSourcing].CertStatus) AS CountOfCertStatus
FROM [tblMaster-ApprovedSourcing]
WHERE ((([tblMaster-ApprovedSourcing].CertStatus) Like "*sales*" Or
([tblMaster-ApprovedSourcing].CertStatus) Like "*corp*" Or
([tblMaster-ApprovedSourcing].CertStatus) Like "*dist*"))
GROUP BY [tblMaster-ApprovedSourcing].[Commodity Code];

This query returns the following:
C 1
M 1

The problem is that I am missing two Commodity Codes from the results.
I would like to show E with a 0 and Unknown with a 0 (both of these
commodity codes are in the tblMaster-ApprovedSourcing). I looked up
the Nz function by reading another post with a similar issue, but I do
not understand how to use it in a query.

The Where clause is excluding those commodities from the
query so they are not there to count. I think you need to
create another query to include the other commodities:

SELECT DISTINCT T.[Commodity Code],
Nz(Q.CountOfCertStatus, 0)
FROM [tblMaster-ApprovedSourcing] As T
LEFT JOIN myquery As Q
ON T.[Commodity Code] = Q.[Commodity Code]

Replace myquery with the name of the query you posted above.

It's possible to do all this in one query using your
original query as a subquery, but it gets rather confusing
and I don't recommend it unless it is necessary.

 

[Inquisitive1]

Marsh,

I had to fiddle with the SQL (I'm not well versed) and realized
everything to the right of "From" had to be on the same line. It worked
exactly how I wanted it to! I don't mind having a lot of queries if
you don't recommend using subqueries.

THANKS!!!!!!!!!!!!!!!!

 

[Marshall Barton]

I had to fiddle with the SQL (I'm not well versed) and realized
everything to the right of "From" had to be on the same line. It worked
exactly how I wanted it to! I don't mind having a lot of queries if
you don't recommend using subqueries.

Glad it's working.

It's not that I abhor subqueries, it's just that they are
more complicated, especially if you find SQL confusing.