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

Calculating score percentages within groups

P: 3
I have problems calculating score percentages within groups.

I have created a boat program in access where the information provided is name, team, boat and score.

The first query I've done is to produce the ranking according to the score, which worked fine.

The second query is about giving 100% to the highest score per boat and then taking the percentage of that score and calculate the rest of the team scores according to that score; below is a sample of the data I have:

in team A:

Player's Name Boat Points
AA 4 175
AB 2 120
AC 1 105
AD 3 295

in team B:

Player's Name Boat Points
BA 4 180
BB 1 170
BC 2 145
BD 3 105

in team C:

Player's Name Boat Points
CA 1 150
CB 2 0
CC 4 185
CD 3 225


After performing the calculations, given that the highest score of each boat should be considered as 100% and the other ones should be a percentage of that score per boat.
I have tried various codes but none seemed to work so far.

Any ideas? suggestions?
Thanks

EC
Jun 1 '07 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,315
I have problems calculating score percentages within groups.

I have created a boat program in access where the information provided is name, team, boat and score.

The first query I've done is to produce the ranking according to the score, which worked fine.

The second query is about giving 100% to the highest score per boat and then taking the percentage of that score and calculate the rest of the team scores according to that score; below is a sample of the data I have:

in team A:

Player's Name Boat Points
AA 4 175
AB 2 120
AC 1 105
AD 3 295

in team B:

Player's Name Boat Points
BA 4 180
BB 1 170
BC 2 145
BD 3 105

in team C:

Player's Name Boat Points
CA 1 150
CB 2 0
CC 4 185
CD 3 225


After performing the calculations, given that the highest score of each boat should be considered as 100% and the other ones should be a percentage of that score per boat.
I have tried various codes but none seemed to work so far.

Any ideas? suggestions?
Thanks

EC
I'm thinking a subquery to select the max by team.
Jun 1 '07 #2

P: 3
I'm thinking a subquery to select the max by team.
how can i do that?
please help.
many thanks.
Jun 1 '07 #3

Rabbit
Expert Mod 10K+
P: 12,315
It would be something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT x.Group, x.Member, x.Score,
  2.    (x.Score / (SELECT Max(Score)
  3.     FROM Table1
  4.     WHERE Group = x.Group)) As Percentage
  5. FROM Table1 As x;
  6.  
Jun 1 '07 #4

P: 3
HI,

I worked the SQL code and works fine in the subqueries.
I did subqueries per boat.
Now I have to link them together in another query but I can't seem to be doing it right.

I have the results of the 4 boats, which are the max of each boat with their percentages. I have created a query which integrates the boats separate subqueries but it gives me the percentages per query in different columns.
How can I integrate them into one column called lets say.. percentage?

Many thanks.

EC
Jun 4 '07 #5

Rabbit
Expert Mod 10K+
P: 12,315
Use a union query.

But you should have been able to do everything in one query if the tables were designed correctly.
Jun 4 '07 #6

Post your reply

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