Solved

SUM top n results for all competitors

Posted on 2016-10-29
4
57 Views
Last Modified: 2016-10-30
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
Comment
Question by:Janez Škrbec
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 41865663
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
 

Author Comment

by:Janez Škrbec
ID: 41865788
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
 
LVL 40

Assisted Solution

by:als315
als315 earned 500 total points
ID: 41866052
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
 

Author Closing Comment

by:Janez Škrbec
ID: 41866078
Thanks, I figured out the last answer by myself just before your response. Thanks for the help.

Regards Janez
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question