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

Janez Škrbec

Asked: 2016-10-29

Advertise Here

Microsoft Access

2 Solutions

LVL 41

als315Commented: 2016-10-30

You should have prioper table structure if you like to use TOP in Access. Each match should be separate record:
Competitor Match Score
Jack 1 25
Jack 2 20
Jack 3 25
Jack 4 7
Jack 5 20
Jack 6 6
........
If table will have this structure, you can use classic query: Select top N from category
Look at sample
DBMatches.accdb

Janez ŠkrbecAuthor Commented: 2016-10-30

Thanks, this solved my question. I have tables structured like this. Maybe I made a mistake in my question because I have written a result from crosstab query.
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.

LVL 41

als315Commented: 2016-10-30

You can add count of matches to the same query:

SELECT tblCompetitors.ID, tblCompetitors.Competitor, Sum(tblData.Score) AS SumOfScore, Count(tblData.Match) AS CountOfMatch
FROM tblCompetitors INNER JOIN tblData ON tblCompetitors.ID = tblData.ID
WHERE (((tblData.Match) In (SELECT TOP 5 Match FROM tblData AS tD WHERE tD.ID = tblCompetitors.ID ORDER BY tD.Score DESC, tD.ID DESC)))
GROUP BY tblCompetitors.ID, tblCompetitors.Competitor
HAVING (((Count(tblData.Match))>3));

Open in new window

Janez ŠkrbecAuthor Commented: 2016-10-30

Thanks, I figured out the last answer by myself just before your response. Thanks for the help.

Regards Janez

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Expand your skills with this month's free Premium course.

SUM top n results for all competitors

Join & Write a Comment Already a member? Login.

Featured Post