469,306 Members | 1,903 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

Showing Percentage in a Query

8
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.


Expand|Select|Wrap|Line Numbers
  1. SELECT FaultCategory, COUNT(*) As [No Fault Totals]
  2.     FROM WorkUnitsFaultsMainTBL
  3.         WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND 
  4.               FaultCategory IN ('No Faults') AND 
  5.               [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt]
  6.                                    AND 
  7.                                    [Forms]![Queries_ReportsFRM]![EndDateTxt]
  8.     GROUP BY FaultCategory 
  9.  
  10. UNION ALL SELECT 'Total Work Units' As FaultCategory, COUNT([WorkUnit]) As [WU Totals] 
  11.     FROM (Select Distinct [WorkUnit] 
  12.         FROM WorkUnitsFaultsMainTBL 
  13.            WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND
  14.            [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] 
  15.                                        AND 
  16.                                        [Forms]![Queries_ReportsFRM]![EndDateTxt]) As vTbl;
Jan 29 '07 #1
12 8410
aldeb
8
As Pink Floyd said Is anybody out there?
Jan 29 '07 #2
ADezii
8,800 Expert 8TB
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.


Expand|Select|Wrap|Line Numbers
  1. SELECT FaultCategory, COUNT(*) As [No Fault Totals]
  2.     FROM WorkUnitsFaultsMainTBL
  3.         WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND 
  4.               FaultCategory IN ('No Faults') AND 
  5.               [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt]
  6.                                    AND 
  7.                                    [Forms]![Queries_ReportsFRM]![EndDateTxt]
  8.     GROUP BY FaultCategory 
  9.  
  10. UNION ALL SELECT 'Total Work Units' As FaultCategory, COUNT([WorkUnit]) As [WU Totals] 
  11.     FROM (Select Distinct [WorkUnit] 
  12.         FROM WorkUnitsFaultsMainTBL 
  13.            WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND
  14.            [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] 
  15.                                        AND 
  16.                                        [Forms]![Queries_ReportsFRM]![EndDateTxt]) As vTbl;
This should point you in the right direction:
Expand|Select|Wrap|Line Numbers
  1. The Percentage No Faults couold be obtained by: Format$([No Faults]/[Total Work Units],"Percent")
Jan 29 '07 #3
aldeb
8
Thank you,

Where do I insert that in my SQL Code?
Jan 29 '07 #4
NeoPa
32,173 Expert Mod 16PB
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.
Jan 29 '07 #5
NeoPa
32,173 Expert Mod 16PB
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.
Jan 29 '07 #6
aldeb
8
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
Jan 30 '07 #7
NeoPa
32,173 Expert Mod 16PB
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.
Jan 30 '07 #8
aldeb
8
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!
Jan 30 '07 #9
NeoPa
32,173 Expert Mod 16PB
Wow, you're a real joy to have around aren't you.
Hey, I guess whining isn't a crime.
Peace.
Jan 30 '07 #10
MMcCarthy
14,534 Expert Mod 8TB
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
Jan 30 '07 #11
AricC
1,892 Expert 1GB
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
Jan 31 '07 #12
aldeb
8
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
Jan 31 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by James Conrad StJohn Foreman | last post: by
2 posts views Thread by Dave | last post: by
1 post views Thread by zufie | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.