Showing Percentage in a Query

[aldeb]

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;

[aldeb]

As Pink Floyd said Is anybody out there?

[ADezii]

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;

This should point you in the right direction:

The Percentage No Faults couold be obtained by: Format$([No Faults]/[Total Work Units],"Percent")

[aldeb]

Thank you,

Where do I insert that in my SQL Code?

[NeoPa]

As Pink Floyd said Is anybody out there?

Is there anybody out there?

However, I'm not sure of the appropriateness in this case. This is a forum supported by members who donate their time, not a paid-for support service with a four-hour SLA.
If you need to bump a thread then feel free to after a day (at least) of inactivity.

MODERATOR.

[NeoPa]

In this case, unless both values are available in the same record of a query this cannot be done.
There are tricks for arranging data to be merged into a single record but generally they depend on the data you're dealing with. I'm afraid your explanation doesn't give enough information about the nature of your data to enable one even to start thinking of a suitable trick.
In general though, you would need to identify the data from individual input records (flag the values you're interested in somehow), group them, then pick out the identified inner values somehow to perform the arithmetic on.

[aldeb]

NeoPa,

Thank you for your information. As far as the inappropriate post please lighten up! I was trying to be friendly and show some humor!! I am sorry if you were offended!! As far as not providing enough info, I beg to differ. I have received several answers from other forums with the same posting. Maybe I did not provide enough info for you. If that is the case again I am sorry.

This is the best solution so far:

Why not split your original post into two queries?
Q1:
CODE
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:
CODE
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:
CODE
SELECT [q1].[No Fault Totals]/[q2].[WU Totals] AS ratio
FROM q1, q2;

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

Shalom

[NeoPa]

I'm sorry you failed to notice the humour in my response.
However, as a moderator I have to (and want to) enforce the rules and guidelines of the site and make members aware of them if they stray across them inadvertantly or otherwise.
In the view of the moderators of the site, this is the best way of reassuring our members (and especially our experts) that they can expect to be treated with respect when visiting or working (donating their free time) here.
I'm sorry if that's not an environment you're comfortable with.

MODERATOR.

[aldeb]

Geez NeoPa, you seem to be pretty uptight as a moderator. I did not disrepect anyone with anything I said in my posts. Who said anything about being uncomfortable with the environment of this forum?. I hope you are not one of those moderators who judges everyone and gets on his/her high horse thinking he/she knows how others feel about things! I apologized in my last post if my earlier post was out of line! I also posted a solution that was provided for my issue from another forum. The lecture is not needed. Thanks for all help!

[NeoPa]

Wow, you're a real joy to have around aren't you.
Hey, I guess whining isn't a crime.
Peace.

[MMcCarthy]

Geez NeoPa, you seem to be pretty uptight as a moderator. I did not disrepect anyone with anything I said in my posts. Who said anything about being uncomfortable with the environment of this forum?. I hope you are not one of those moderators who judges everyone and gets on his/her high horse thinking he/she knows how others feel about things! I apologized in my last post if my earlier post was out of line! I also posted a solution that was provided for my issue from another forum. The lecture is not needed. Thanks for all help!

aldeb

NeoPa was simply trying to guide you in the quickest way to get a solution in this forum. Although you said you received answers from other forums you obviously didn't get a solution. Following his advice and posting some extra details would give the experts more to work with and therefore you would be more likely to find an appropriate solution.

Nobody is trying to be uptight but simply spending their spare time trying to help you find a solution to your problem. One of the few thinks we do insist on in this forum is respect for the experts as they donate their valuable free time. NeoPa was simply trying to advise you that if you wish to attract the attention of experts of which we have some excellent ones then a different approach may be more successful.

ADMIN

[AricC]

As Pink Floyd said Is anybody out there?

Typical, I think the track record of the Access forum speaks for itself. Pay no attention MS Access Experts you're doing a great job!


Aric

[aldeb]

Wow, you're a real joy to have around aren't you.
Hey, I guess whining isn't a crime.

Now this I would say is an inappropriate post and disrespectful also!

Typical, I think the track record of the Access forum speaks for itself. Pay no attention MS Access Experts you're doing a great job!

Way to jump in there Aric with your solution to the problem. I am glad you volunteer
your time to help with his forum! Providing answers to the actual posted problem
might be a little more beneficial than jumping in with your two cents to something
that you should not be involved in!

If these are the typical responses to issues on this forum you may remove me if it makes
you all feel better!

Shalom