Problem with Null, need help

[Funkbuqet]

Here is my problem.

I am trying to make a query that will produce a table with a count of projects in different stages. I have 3 groups A,B, and C. Each one of those groups has a number of projects that can be in stages 1, 2, 3, or 4. Each project has a series of check boxes illustrating when a stage is completed. So I created 4 independent queries to return the count of projects in a given stage per group. Each of these 4 queries return only 2 columns of data - Group and Count. And they all work well. I then created a 4th query to tie them all together (relating the Group column of the queries together). The issue I am having is only group B has projects in Stage 4, so the stage 4 query only returns a value for Group B. Ergo, when i try to put them all together in the 5th query it only returns results from Group B because it is the only group all of the other share. if I remove the stage 4 query, the final query work perfectly.

My question is; is there a way to force the stage 4 query to return zero as a result for Groups A and C? OR Is there a way to set up the final query so that when is sees Null values for Group A and C in Stage 4, it inserts a zero value?

Any thoughts you have would be appreciated. Thank you.

 

[vbaInet]

You can perform a count of all stages per group in just one query.

Let's see a sample of the raw data before counting.

 

[Funkbuqet]

Ok here is a dummy database set up similar to mine to illistrate the problem I am having. Keep in mind in my database both of the Tables have many other fields, but I only included the ones relevant to query at hand. Also, FYI this database has been used for quite some time and is populated with copious data. So, changing the structure ot the data tables is not really a viable solution for me. Thanks for all your help.

 

[vbaInet]

Look at qryNewCounts to see how it was done.

 

[Funkbuqet]

That works great! Thank you very much!

 

[vbaInet]

You're welcome!