469,091 Members | 1,111 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Gaining percentage value from a query

Hi i am trying to gain a percentage from values i recive from a query (table values attachment displays values from query) as you can see from the image award name BAC has 7 male, 32 no, and 3 females. i would like this to show as a percentage instead. I am using a access query to get the values in the image "table values" .i will be putting the values into a report. The current SQL i am using to get the values show in the image is.

Expand|Select|Wrap|Line Numbers
  1. SELECT Student.Gender, Count(Student.Gender) AS CountOfGender, Awards.AwardName
  2. FROM Student INNER JOIN Awards ON Student.AwardID = Awards.AwardID
  3. GROUP BY Student.Gender, Awards.AwardName;
Any help will be greatly appricated, also if any more info is needed please let me no.
Thank you

Attached Images
File Type: jpg table values.jpg (53.5 KB, 260 views)
Nov 7 '11 #1
7 1739
Rabbit
12,516 Expert Mod 8TB
You can use a query to get the count by group and then join that into your existing query so you have all the variables you need to calculate the percentage.
Nov 7 '11 #2
hi thanks for the reply. i am new to access and everything that goes with it. By what you say do you mean create a new query in access and then somehow link them together. or add more code to the existing query to make it do what i am hoping?
thanks again
Nov 8 '11 #3
Rabbit
12,516 Expert Mod 8TB
Basically
Expand|Select|Wrap|Line Numbers
  1. SELECT Group, Individual, COUNT(*) / GroupCount AS Percentage
  2. FROM someTable AS a
  3. INNER JOIN (
  4.    SELECT Group, COUNT(*) AS GroupCount
  5.    FROM someTable
  6.    GROUP BY Group
  7. ) AS b
  8. ON a.Group = b.Group
  9. GROUP BY Group, Individual
Nov 8 '11 #4
hi thanks again for the quick reply i have changed you code to the appropriate values and still run up with a error. the error is at the bottom.

Expand|Select|Wrap|Line Numbers
  1. SELECT Awards.AwardName, Gender, COUNT(*) / GroupCount AS Percentage 
  2. FROM student, Awards AS a 
  3. INNER JOIN ( 
  4.    SELECT Awards.AwardName, COUNT(*) AS GroupCount 
  5.    FROM student, Awards 
  6.    GROUP BY Awards.AwardName 
  7. ) AS b 
  8. ON a.AwardName = b.Awards.AwardName 
  9. GROUP BY Awards.AwardName, Gender

Attached Images
File Type: jpg error.jpg (13.2 KB, 208 views)
Nov 8 '11 #5
Rabbit
12,516 Expert Mod 8TB
Looks like you'll have to split out the count into its own subquery and then join everything back together.
Nov 9 '11 #6
NeoPa
32,159 Expert Mod 16PB
Sorry 315, but that's not remotely similar to Rabbit's SQL. You've introduced an extra table to create a cartesion product (See SQL JOINs). You cannot expect such changed SQL to work in a similar way.
Nov 12 '11 #7
wSpang
1
You could also uses the DCount("Field","TableName","Criteria") to retrieve the value to calculate your percentage from the base table, as well.
Nov 12 '11 #8

Post your reply

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

Similar topics

7 posts views Thread by Rob Meade | last post: by
reply views Thread by Horst Rotenholzer | last post: by
22 posts views Thread by Les Juby | last post: by
3 posts views Thread by thomas goodwin | last post: by
6 posts views Thread by jez123456 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.