If No Faults are Zero-Show a Zero

[netrusher]

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;

 

[genomon]

Replace Count(*) AS [No Fault Totals]
with
Nz(Count(*),0) AS [No Fault Totals]
Not sure if Nz works with aggregate functions, but it's worth a shot.


Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[netrusher]

I used the below code and am still getting the same results. If the date range has NO No Faults it is not putting in a zero.

SELECT WorkUnitsFaultsMainTBL.FaultCategory, Nz(Count(*),0) 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;

 

[lespaul]

In order to report on 'nothing' (which is what you're trying to do), you need a source that shows everything you're trying to list.

If you had a table that contained all the BuildIDs, you could then find all the buildIds that don't have any data attached to them.

So, let's say the table with the BuildId's is called AllBuilds, and it contains ("E010","C809","F001","C810","F187","A910","M173","M174").

Your query could be modified to:

SELECT WorkUnitsFaultsMainTBL.FaultCategory, Count(*) AS [No Fault Totals]
FROM WorkUnitsFaultsMainTBL
[b]LEFT JOIN AllBuilds on AllBuilds.BuildID = WorkUnitsFaultsMainTBL.BuildID[/b]
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;

read the article below on JOINS to understand the difference in INNER, LEFT and RIGHT joins.

HTH




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[netrusher]

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]));

 

[lespaul]

can you give some sample data from WorkUnitsFaultsMainTBL and the expected results and how you get those results? It's a lot easier with a "picture".

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[netrusher]

Leslie,

I am not sure how to do that or really what you want. The code I have listed above works just the way I want it to with the exception of Work Units. I really only want totals of unique Work Units. Does anyone know how to modify the SQL above to ask for Unique Work Units only?

 

[netrusher]

Leslie:

Below are the results I am seeking and I get these results depending on whether or nor there are any No Faults. The Work Unit totals are total records and not Unique Work Units. The actual Unique Work Unit totals for below should be 89. I really need to know how to solicit Unique Work Units in the query I have shown above. 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

 

[lespaul]

Does anyone know how to modify the SQL above to ask for Unique Work Units only

we have to see how the data is structured in order to determine how to see what you want.

Where does the NoFaultTotal of 7 come from? I have no idea, therefore I have no idea how to modify your query to get that information.

I am not sure how to do that or really what you want.

Like this:

The table looks like:
[tt]
WorkUnitFaultsMainTBL
BuildID FaultCategory WorkUnit TodaysDate
E010 No Faults ???? 5/5/2007
C809 SomeOtherCategory? ???? 5/5/2007
add additional records
[/tt]

Using the sample data above, I would like a query that returns the following information:

[tt]
Fault Type NoFaultTotals Total Work Units
using sample data from above show results and explain any logic used to get those results[/tt]

 

[netrusher]

Leslie the table has too many fields to list. I am querying the table for the total of NoFaults. The NoFaults is one of the options for a field in the table named FaultCategory. The FaultCategory Field in the table has 3 possible results.

Mechanical
Cosmetic
No Faults

My query is just trying to find how many NoFaults there are based on a date range. In my examples above based on date range one of them had 0 NoFaults and the other had 7 NoFaults. The WorkUnit totals is total records based on the daterange. In stead of the WorkUnit totals giving me total WorkUnit Records I want it to give me Unique WorkUnit totals based on the date range.

I hope this helps. If not, I do not know how to explain any better as to what I am trying to accomplish but I sure appreciate any and all help.

 

[lespaul]

you don't need to list all the fields, just the ones you're using in the query.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[netrusher]

With the Query in Design Mode below is each Field accross.

Fault Type: 'No Faults'

NoFault Totals: Sum(IIf([FaultCategory]='No Faults',1,0))

Total Work Units: Count(WorkUnitsFaultsMainTBL.WorkUnit)

Pct NoFaults: FormatPercent(Sum(IIf([FaultCategory]='No Faults',1,0))/Count([WorkUnit]),2)

BuildId

TodaysDate

 

[netrusher]

Leslie,

Did my last post help with anything?

 

[netrusher]

HELP! Can anyone out there help with this problem I am having?

 

[netrusher]

Since I cannot get any anwwers to this post how do I close this down?