Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

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
0
Janez Škrbec
Asked:
Janez Škrbec
  • 2
  • 2
2 Solutions
 
als315Commented:
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.
0
 
als315Commented:
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

0
 
Janez ŠkrbecAuthor Commented:
Thanks, I figured out the last answer by myself just before your response. Thanks for the help.

Regards Janez
0
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

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.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now