If No Faults are Zero-Show a Zero

[aldeb]

Below is the SQL Code for a Query that I have. It gives me the total of No Faults. My issue is if there are no No Faults I would like to show a zero. Is there anyway to accomplish this?

SELECT WorkUnitsFaultsMainTBL.FaultCategory, Count(*) AS [No Fault Totals]
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")) And ((WorkUnitsFaultsMainTBL.FaultCategory) In ('No Faults')) And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt))
GROUP BY WorkUnitsFaultsMainTBL.FaultCategory;

 

[Alc]

I think this should do it.

Below is the SQL Code for a Query that I have. It gives me the total of No Faults. My issue is if there are no No Faults I would like to show a zero. Is there anyway to accomplish this?

SELECT WorkUnitsFaultsMainTBL.FaultCategory, [B]IIf(IsNull(Count(*)),0,Count(*))[/B] AS [No Fault Totals]
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")) And ((WorkUnitsFaultsMainTBL.FaultCategory) In ('No Faults')) And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt))
GROUP BY WorkUnitsFaultsMainTBL.FaultCategory;

 

[aldeb]

I am using the code below and still getting no results if there are No Faults in the date range? Did I do something wrong?

SELECT WorkUnitsFaultsMainTBL.FaultCategory, IIf(IsNull(Count(*)),0,Count(*)) AS [No Fault Totals]
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")) And ((WorkUnitsFaultsMainTBL.FaultCategory) In ('No Faults')) And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt))
GROUP BY WorkUnitsFaultsMainTBL.FaultCategory;

 

[Alc]

Are there definitely entries for the criteria you list?

 

[aldeb]

Alc,

In the where statement for the below code on the date range I am selecting there are no WorkUnits with No Faults. That is the reason for my posting. If in the date range there happens to be no records with No Faults I want to show Zero. If there are entry's with [/b]No Faults[/b] I get the count of the No Faults. Hope this makes sense.

And ((WorkUnitsFaultsMainTBL.FaultCategory) In ('No Faults'))

 

[aldeb]

Everyone:

The code below gives me the totals of No Faults both ways. It gives me zero if there are no No Faults and 7, 8, 14, etc. If there are No Faults. My dilemma with the code below is it is giving me total Work Units and I really need Distinct Work Units. Work Units can have multiple Faults. Can anyone show me how to insert code into the code below that will give me only Distinct Work Units totals?

SELECT  'No Faults' AS [Fault Type], Sum(IIf([FaultCategory]='No Faults',1,0)) AS [NoFault Totals], Count(WorkUnitsFaultsMainTBL.WorkUnit) AS [Total Work Units]
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")) AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]));

 

[Alc]

There may be multiple ways around this, but I would use two queries. One would select all possible fault categories, the second would Left Join that query to the one you currently have. This way you would get a list of all categories and, where applicable, a number. The Iif(IsNull) bit would handle those rows where a category has no number.

 

[aldeb]

Alc,

Thanks and I may try that but based on the SQL I provide above I am getting everything I need but Unique WorkUnit count. Does anyone know how to modify the SQL to request Unique WorkUnits only. In my examples below I am getting 0 in No Faults when there are no NoFaults and 7 in this example when there are 7 NoFaults. The WorkUnits total is based on total records of 197. In reality there is really only 89 Unique WorkUnits and that is what I am trying to get. My code for the NoFaults is now working. I just want to get the Unique WorkUnits if possible. Thanks for all of your help.


Fault Type NoFault Totals Total Work Units
No Faults ---------0---------------197

Fault Type NoFault Totals Total Work Units
No Faults ---------7---------------197

 

[aldeb]

Since I cannot get any solutions to this post, how can I close this down?

 

[neileg]

Since I cannot get any solutions to this post, how can I close this down?

Stop posting!