Force zeros to display in a count (totals) query (1 Viewer)

SatanLuciferJones · Jun 26, 2019

I have created a simple count query:

Code:

SELECT ECapQuery.TestID, Count(ECapQuery.YNavg) AS cnZ
FROM ECapQuery
WHERE (((ECapQuery.YNavg)=0))
GROUP BY ECapQuery.TestID;

However, I would like it to display all of the ECapQuery.TestIDs, even if the count is 0. Is there a way to force the query to display the count total for each ECapQuery.TestID?

theDBguy · Jun 26, 2019

You will have to join the main table listing all possible TestIDs with your query using an OUTER JOIN.

plog · Jun 27, 2019

The query you posted, call it 'sub1'. Now create a new query using it and the table that lists all TestID values-- let's call that table 'TestMain'. JOIN them via TestID fields then change that JOIN line to show all from TestMain. Bring down TestId from TestMain and then create a calculated field using this:

TestIDTotal: Nz(cnZ, 0)

That query will do what you want.

SatanLuciferJones · Jun 27, 2019

Thanks plog, that was easy to follow and gave me the desired results. Thanks again!

Force zeros to display in a count (totals) query (1 Viewer)

SatanLuciferJones

New member

theDBguy

I’m here to help

plog

Banishment Pending

SatanLuciferJones

New member

Similar threads

Users who are viewing this thread