Solved

SUM top n results for all competitors

Posted on 2016-10-29
4
32 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 39

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 39

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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 …

911 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now