Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SUM top n results for all competitors

Posted on 2016-10-29
4
Medium Priority
?
68 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 2000 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 2000 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

661 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