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]

As Pink Floyd said Is anybody out there?

 

[PaulBricker]

I don't know if you can do it in one query. You could add two addtional fields to the query in the locations I've marked in bold

SELECT FaultCategory, COUNT(*) As [No Fault Totals], 0 As TotalCount


UNION ALL SELECT 'Total Work Units' As FaultCategory,0 As TotalCount, COUNT([WorkUnit]) As [WU Totals]


This will give you a result that looks like this

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

From there, you might put that in a new query, add the columns and then divide the results to get your %. That's about the only solution outside a Report that I could think of.

Paul

 

[netrusher]

Thanks Paul!

How would I add the columns and divide the results in a new query?

 

[AlexCuse]

NetRusher - I think your best solution would be to create a new query that returns NoFaults and TotalWorkUnits in the same row. This will allow you to calculate percentage directly in your query.

Are you very attached to the result set format that your current query is giving?

Ignorance of certain subjects is a great part of wisdom

 

[netrusher]

Alex,

I am willing to try these different things. My issue is creating queries. I am still trying to learn. I do I create a query show the results in the same row and then to the division to show the percentage directly in my query?

 

[AlexCuse]

NetRushe -

It'd be something like this.

select 'No Faults' as FaultCategory
, sum(iif(FaultCategory = 'No Faults', 1, 0)) as NoFaults
, count([Column that is always populated]) as TotalWorkUnits
,FORMATPERCENT(sum(Iif(FaultCategory = 'No Faults', 1, 0))
/ Count([Column that is always populated]), 2)
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]
group by FaultCategory

(not tested)

The '2' at the end of the FORMATPERCENT portion is how many decimal places you want to take your percentage out to. 'Column that is always populated' can be any column, preferably an ID column or something like that, that will not contain any null values.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[netrusher]

Thanks Alex,

I am getting the following results:
I substituted WUID for Column that is always populated.

FaultCategory NoFaults TotalWorkUnits Expr1003
NoFaults 9 9 100.00%
NoFaults 95 95 100.00%
NoFaults 975 975 100.00%
NoFaults 86 86 100.00%

select 'No Faults' as FaultCategory
, sum(iif(FaultCategory = 'No Faults', 1, 0)) as NoFaults
, count([WUID]) as TotalWorkUnits
,FORMATPERCENT(sum(Iif(FaultCategory = 'No Faults', 1, 0))
/ Count([WUID]), 2)
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]
group by FaultCategory

 

[traingamer]

Why not split your original post into two queries?
Q1:

SELECT 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

and Q2:

SELECT  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;

Then a query that links them. Q3:

SELECT [q1].[No Fault Totals]/[q2].[WU Totals] AS ratio
FROM q1, q2;

I leave the error checking / divide by zero checks to you.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[AlexCuse]

You know what I think the problem is, that I hard coded the value for FaultCategory, so of course it is always going to say 'No Fault'. Maybe you should use the alias FCAT or something like that for the column?

SOrry about that.

Also - TrainGamer's suggestion is a good one. Especially if you see yourself needing to use the values in the queries for other calculations. I try to keep things to one query just to keep my databases neat (and because there are not a lot of values that I need to store to use elsewhere), but because you could be using these results elsewhere it makes a lot of sense to store as separate queries.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[netrusher]

Everyone:

Thanks for all of your input. Greg, when use the SQL Below, all I get is the Ratio or Pct. I need Total Work Units, Total No Faults and Pct.

SELECT [PCT-NoFaultTotals-1-3TON].[No Fault Totals]/[PCT-WorkUnitTotals-1-3TON].[WU Totals] AS ratio
FROM [PCT-NoFaultTotals-1-3TON], [PCT-WorkUnitTotals-1-3TON];

Alex, I am struggling to find out where to change to FCAT for the change you recommended:

SELECT 'FCat' AS FaultCategory, sum(iif(FaultCategory = 'No Faults', 1, 0)) AS NoFaults, count([WUID]) AS TotalWorkUnits, FORMATPERCENT(sum(Iif(FaultCategory = 'No Faults', 1, 0))
/ Count([WUID]), 2)
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]
GROUP BY FaultCategory;

I am still getting four rows of information. No Faults are all Zero now. I know I am probably doing something wrong and I appreciate your alls help.

I am hoping for one Row with Total NoFaults, Total WorkUnits and Pct of NoFaults. I think we are close so please keep helping!!!

 

[traingamer]

...all I get is the Ratio or Pct. I need Total Work Units, Total No Faults and Pct.

Then include them as well

SELECT [b][PCT-NoFaultTotals-1-3TON].[No Fault Totals],[PCT-WorkUnitTotals-1-3TON].[WU Totals],[/b][PCT-NoFaultTotals-1-3TON].[No Fault Totals]/[PCT-WorkUnitTotals-1-3TON].[WU Totals] AS ratio
FROM [PCT-NoFaultTotals-1-3TON], [PCT-WorkUnitTotals-1-3TON];

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[AlexCuse]

And for mine, you would replace the

AS FaultCategory

with AS [Whatever]

Ignorance of certain subjects is a great part of wisdom

 

[PaulBricker]

Sorry I've been out of the office all afternoon and lost track of this. If you want to follow my suggestion, you would create a new query using the results of the query you first posted with my revisions. Just add the two fields with the numbers in them. On the field line, if you don't like what the name is you can change it so you have Total Work Units and Total No Fault as the names. Then you would turn on the totals. Sum the two fields that have the numbers. Then in the third column, you would put
Pct:[Total No Fault]/[Total Work Units]
and set the Total row to Expression.

Give it a try. If you have problems with the names, post back.

Paul

 

[netrusher]

Greg,

Thank you for your help! How do I shorten the result? Right now it goes out 15 places. I would like to shorten it to only two places.

 

[netrusher]

Alex,

I still am interested in your help. I am getting these results:

FCat NoFaults TotalWorkUnits Expr1003
No Faults 0 9 0.00%
No Faults 0 101 0.00%
No Faults 0 986 0.00%
No Faults 87 87 100.00%

SELECT 'No Faults' AS FCat, Sum(IIf(FaultCategory='No Faults',1,0)) AS NoFaults, Count(WorkUnitsFaultsMainTBL.WUID) AS TotalWorkUnits, FormatPercent(Sum(IIf(FaultCategory='No Faults',1,0))/Count([WUID]),2) AS Expr1
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]))
GROUP BY WorkUnitsFaultsMainTBL.FaultCategory;

 

[AlexCuse]

netrusher -

You need to remove your group by from that query, and it should give this result

FCat NoFaults TotalWorkUnits Expr1003
No Faults 87 1183 7.35%

As far as your other question, have a look at the formatnumber function. It works very similarly to formatpercent that you've used already.

HOpe this helsp,

ALex





Ignorance of certain subjects is a great part of wisdom

 

[netrusher]

Alex,

Thanks,

Now I am getting 0 for NoFaults??

Also, do you know how I can ensure my TotalWorkUnits gives me the total of DISTINCT WorkUnits? Each WorkUnit can be listed multple times due to having multiple faults. For my WorkUnits Total I only want a count of Distinct WorkUnits.

I really appreciate your help!!!!

SELECT 'FCat' AS FaultCategory, sum(IIf(FaultCategory='No Faults',1,0)) AS NoFaults, count([WUID]) AS TotalWorkUnits, FORMATPERCENT(sum(IIf(FaultCategory='No Faults',1,0))/Count([WUID]),2)
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
;

 

[AlexCuse]

Your problem is this:

SELECT 'FCat' AS FaultCategory

You do NOT want to call this derived column FaultCategory, because if you do, your IIF statement will look at this phony column, rather than the column named FaultCategory in your table. This is why when you set the column's value as 'No Faults', all records were being returned in the sum, and if you set it to something else, no records are being returned.

I would replace the piece of code I copied with something like this:

SELECT 'No Faults' AS FCAT

and see if you get your desired result.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[netrusher]

Alex,

that works great! Now is there a way to ensure that I am getting Distinct WorkUnit Totals and not Total WorkUnit Faults Totals since WorkUnits can have multiple entrys?