469,573 Members | 1,684 Online

# Calculating score percentages within groups

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

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
5 2575
Rabbit
12,516 Expert Mod 8TB
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

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
I'm thinking a subquery to select the max by team.
how can i do that?
many thanks.
Jun 1 '07 #3
Rabbit
12,516 Expert Mod 8TB
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
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
12,516 Expert Mod 8TB
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