Force zeros to display in a count (totals) query

[SatanLuciferJones]

I have created a simple count query:

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]

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

 

[plog]

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]

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