Showing Percentage in a Query 3

[netrusher]

Below is the code I am using in a query that produces results that look like:

Fault Category No Fault Totals
No Faults 77
Total Work Units 521

I would like to also show in the query the percentage of No Faults. In this instance I would need to divide
the 77 no faults by the 521 total work units. Is there a way to do this and show the answer in the query.
I do not want this in a report, just the query.

SELECT FaultCategory, COUNT(*) As [No Fault Totals]
    FROM WorkUnitsFaultsMainTBL
        WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND 
              FaultCategory IN ('No Faults') AND 
              [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt]
                                   AND 
                                   [Forms]![Queries_ReportsFRM]![EndDateTxt]
    GROUP BY FaultCategory 
    
UNION ALL SELECT 'Total Work Units' As FaultCategory, COUNT([WorkUnit]) As [WU Totals] 
    FROM (Select Distinct [WorkUnit] 
        FROM WorkUnitsFaultsMainTBL 
           WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND
           [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] 
                                       AND 
                                       [Forms]![Queries_ReportsFRM]![EndDateTxt]) As vTbl;

 

[netrusher]

I just verified that my NoFaults count is correct but my TotalWorkUnits count is based on all WorkUnit records and not Distinct WorkUnits that I need.

 

[AlexCuse]

First thing you could try is changing your query to a SELECT DISTINCT

It may be as simple as that.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[netrusher]

Thanks Alex!

I already tried that and am still getting the same WorkUnit Totals. Anything else you can think of? I sure appreciate your help. Did I put DISTINCT in the correct location?

SELECT DISTINCT 'No Faults' AS [Fault Type], Sum(IIf([FaultCategory]='No Faults',1,0)) AS [NoFault Totals], Count(WorkUnitsFaultsMainTBL.WUID) AS [Total Work Units], FormatPercent(Sum(IIf([FaultCategory]='No Faults',1,0))/Count([WUID]),2) AS [Pct NoFaults]
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]));

 

[AlexCuse]

I was afraid of that. What you are going to have to do is use a derived table, which is basically a query within a query. This is done assuming that the columns referenced in your query are the only ones that matter in determining whether or not a record is unique, so you may need to add a few.

Here is the whole query:

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

Pay Close attention tot this part:

(
select distinct [FaultCategory]
, WUID
, BuildID
, TodaysDate
from WorkUnitsFaultsMainTBL
) a

This is what I mean by 'derived table' (aka subquery). This query is treated as a table by your outer query, so basically you are selecting from (query result) rather than an entire table.

You'll notice that I replaced WorkUnitsFaultsMainTBL with 'a' in what was already written of your query. The 'a' after this derived table (subquery) is its' alias. Using an alias allows you to refer to a table in your query as something shorter (like 'a', rather than 'WOrkUnitsFaultsMainTBL'). It is not always necessary, but using aliases is a good practice to get into when working with derived tables.

I hope that this helps you out,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[netrusher]

Alex, Thanks again!

I have fooled around with this but I think I am in way over my head.

This is done assuming that the columns referenced in your query are the only ones that matter in determining whether or not a record is unique, so you may need to add a few.

If I understand what you are saying, I do not have any columns in this query that determine if a record is unique. The only field that determines if the record is unique is WUID (Auto Field). The WorkUnit Field is the one that I need to pull Unique WorkUnits from. I have tried to add this Field WorkUnit to the query with no luck.

Also I do not understand where you have Select Distinct [FaultCategory]. I am sure you have explained that here and it zoomed over me. Again, the WorkUnit is what I need to be DISTINCT. I am sorry for being such a pain and I hope I have explained myself but I sure am stuck once again. I hope you have some more help and advice for me.

I have changed the WUID to WorkUnit. This is the field where you stated earlier had to always have something in it. The WUID is my AutoNumber of the Table. The WorkUnit Field is where I need a DISTINCT count of WorkUnitTotals. The query is still the same with WorkUnit in place of WUID so I really do not think what I changed is pertinent to my issue.

I have not changed the code yet to reflect the Alias that you used. When I did that there were no other Fields available for me to select. I will do it in the future as soon as I know what to add to come up with my DISTINCT WorkUnitTotals. I am really learning here (trying to) and understand what you said about derived but of course I do not fully understand.

SELECT DISTINCT 'No Faults' AS [Fault Type], Sum(IIf([FaultCategory]='No Faults',1,0)) AS [NoFault Totals], Count(WorkUnitsFaultsMainTBL.WorkUnit) AS [Total Work Units], FormatPercent(Sum(IIf([FaultCategory]='No Faults',1,0))/Count([WorkUnit]),2) AS [Pct NoFaults]
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]));

 

[AlexCuse]

NetRusher - I see what you need to do, and I will post something for you later. In the meantime, Greg's solution is working for you, right? I am still thinking his is the way to go, but the other may still be something good for you to learn.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[netrusher]

Alex,

Thanks for the help and the opportunity to learn.

 

[AlexCuse]

NetRusher-

On further review, I realized that to do what you need would just be a pretty horrid query when done all at once. It is possible using a ton of subqueries, but Greg's solution is much cleaner and is certainly the way to go.

Have you had any problems getting the correct data into that one?

Good LUck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[netrusher]

NO. It takes two other queries to make the one but it seems to be working!

Thanks again.