By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Gaining percentage value from a query

P: 44
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, 246 views)
Nov 7 '11 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,421
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

P: 44
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
Expert Mod 10K+
P: 12,421
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

P: 44
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, 194 views)
Nov 8 '11 #5

Rabbit
Expert Mod 10K+
P: 12,421
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
Expert Mod 15k+
P: 31,709
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

P: 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.