Janez Škrbec
asked on
SUM top n results for all competitors
I have a table matches with multiple competitors and for different matches with points:
Competitor Match1 Match2 Match3 Match4 Match5 Match6
Jack 25 20 25 7 20 6
Mike 20 25 20 25 15 25
Tom 15 10 15 15 25 15
John 10 15 10 6 10 20
I want to sum this matches that sum uses top 5 points from all matches for example:
Competitor Match1 Match2 Match3 Match4 Match5 Match6 SUM
Jack 25 20 25 7 20 6 97
Mike 20 25 20 25 15 25 115
Tom 15 10 15 15 25 15 85
John 10 15 10 6 10 20 65
In excel this function is LARGE but I do not know what to use in access because because if I use SELECT TOP 5 it does not do the job because does not use the number 7 in Jack's case
Competitor Match1 Match2 Match3 Match4 Match5 Match6
Jack 25 20 25 7 20 6
Mike 20 25 20 25 15 25
Tom 15 10 15 15 25 15
John 10 15 10 6 10 20
I want to sum this matches that sum uses top 5 points from all matches for example:
Competitor Match1 Match2 Match3 Match4 Match5 Match6 SUM
Jack 25 20 25 7 20 6 97
Mike 20 25 20 25 15 25 115
Tom 15 10 15 15 25 15 85
John 10 15 10 6 10 20 65
In excel this function is LARGE but I do not know what to use in access because because if I use SELECT TOP 5 it does not do the job because does not use the number 7 in Jack's case
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, I figured out the last answer by myself just before your response. Thanks for the help.
Regards Janez
Regards Janez
ASKER
Maybe one more question. I also have to show competitors who were on at least 3 matches and the sum of results are from top5 matches. So second part is solved what about to include in this query competitors that were on at least 3 matches.