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
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
0
Janez ŠkrbecAuthor Commented:
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.
SELECT tblCompetitors.ID, tblCompetitors.Competitor, Sum(tblData.Score) AS SumOfScore, Count(tblData.Match) AS CountOfMatchFROM tblCompetitors INNER JOIN tblData ON tblCompetitors.ID = tblData.IDWHERE (((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.CompetitorHAVING (((Count(tblData.Match))>3));
An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
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