?
Solved

SUM top n results for all competitors

Posted on 2016-10-29
4
Medium Priority
?
78 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
  • 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

621 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