Solved

SUM top n results for all competitors

Posted on 2016-10-29

Microsoft Access

2 solutions

64 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

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

Join The Community

4 Comments

LVL 40

Accepted Solution

by:als315

als315 earned 500 total points

ID: 418656632016-10-30

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

Author Comment

by:Janez Škrbec

ID: 418657882016-10-30

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.

LVL 40

Assisted Solution

by:als315

als315 earned 500 total points

ID: 418660522016-10-30

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

Author Closing Comment

by:Janez Škrbec

ID: 418660782016-10-30

Thanks, I figured out the last answer by myself just before your response. Thanks for the help.

Regards Janez

Featured Post

Question has a verified solution.

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

Unorthodox Phishing Attempt Seeks Email Password

Article by: Dermot

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.

Tax Refund Phishing Scam – Protect Your Money

Article by: Dermot

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it

MS Access – Basic Query Design

Video by: Jim

Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

Microsoft Access

Polish Reports in Access

Video by: crystal

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…