Need zero to show up in column if no fault totals exist

[netrusher]

Below is the code I have for a query. It shows me by Column:

Truck.....Category.....SystemGroup.....FaultTotals

My problem is if there is no FaultTotals nothing shows up. I would still like for the
Four Columns to show data and have a 0 in the FaultTotals.

How can I accomplish this?

SELECT "1-3" AS Truck, "Cosmetic" AS Category, WorkUnitsFaultsMainTBL.SystemGroup, Count(*) AS FaultTotals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)="Cosmetic") AND ((WorkUnitsFaultsMainTBL.TodaysDate) 
Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup
HAVING (((WorkUnitsFaultsMainTBL.SystemGroup)="CtWT"))
ORDER BY Count(*) DESC;

 

[PHV]

Have you a table with all possible SystemGroup ?
If so you may play with a LEFT JOIN.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[netrusher]

I do have such a table but could you give me an example of how to do a left join to come up with the zero?

 

[netrusher]

In a nutshell I am totaling all Cosmetic Faults based on the SystemGroup CTWT and a Date Range. If there are
no Cosmetic CTWT Faults for the DateRange I want a zero. The query works great as long as there is at least
One FaultTotal for the Date Range.

 

[netrusher]

I received some code from someone on another forum to help
solve my issue. I had to modify it and I am now getting 0
in the TotalFaults Column where there are no faults for
the date range which is what I wanted. My problem is I do
not understand how or why. Can some of the experts out
there tell me how and why by looking at the code below.

SELECT "1-3" AS Truck, "Cosmetic" AS Category, 
Count(*) AS FaultTotals, "CTWT" 
AS [System Group]
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)="Cosmetic") 
AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] 
And [Forms]![Queries_ReportsFRM]![EndDateTxt]) 
AND ((WorkUnitsFaultsMainTBL.BuildID) 
In ("E010","C809","F001","C810","F187","A910","M173","M174")) 
AND ((WorkUnitsFaultsMainTBL.SystemGroup)="CtWT"));