Results 1 to 13 of 13
  1. #1
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151

    Wink How to get the highest 3 scores for each competitor

    I'm a bit embarrassed to ask this, but I can't seem to find a solution.

    I have a query that reads a yearly score table.



    The query sorts on Surname and event scores (descending)

    I want the query to return the top 3 scores for each competitor over the yearly competition. (they may have competed up to 26 times in that period)

    Should be easy, but it has be stumped

    Thanks in advance.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi Paul

    Thanks for that. I did find that before I posted, however I just don't understand it

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Hard to adapt it without knowing your structure. Perhaps you can attach a sample db?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi Paul

    Quote Originally Posted by pbaldy View Post
    Hard to adapt it without knowing your structure. Perhaps you can attach a sample db?
    I think my biggest problem is that I'm working with a calculated field in the Query. The events run in a number or "rounds" that I like to record and manipulate individually, or sum as required. This is what is doing my head in

    The code below shows the query as it currently stands and works. However I only want it to return the top n (say 3 at this time) for each competitor.

    Code:
    SELECT ShootScores.ID, Members.[Last Name], Members.[First Name], Sum([ShootScores]![Round1Score]+[ShootScores]![Round2Score]+[ShootScores]![Round3Score]+[ShootScores]![Round4Score]) AS TotalScore
    FROM Members INNER JOIN ShootScores ON Members.[Member ID] = ShootScores.[Member ID]
    GROUP BY ShootScores.ID, ShootScores.[Member ID], Members.[Last Name], Members.[First Name]
    ORDER BY ShootScores.[Member ID], Sum([ShootScores]![Round1Score]+[ShootScores]![Round2Score]+[ShootScores]![Round3Score]+[ShootScores]![Round4Score]) DESC;
    Click image for larger version. 

Name:	Capture.JPG 
Views:	44 
Size:	44.6 KB 
ID:	40367

    Thanks for looking at this.

    Happy to post the DB if you need it.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    1. In your opening post you mentioned 'yearly competitions'. Does this mean, there are results from different years in ShootScores table? When yes, then you need a table tblEvents: EventID, EventDate, ...;
    2. The table ShootScores Is not normalized. Consider tblScores: ScoreID, EventID, MemberID, RoundNo, Score, ...

    You get now e.g. total scores per all members per all events as:
    Code:
    SELECT ev.EventDate, mem.FirstName, mem.LastName, SUM(sc.SCORE) AS EventTotal 
    FROM (tblScores sc INNER JOIN tblMembers mem ON mem.MemberID = sc.MemberID) INNER JOIN tblEvents ev ON ev.EventID = sc.EventID
    GOUP BY SELECT ev.EventDate, mem.FirstName, mem.LastName
    Now it is unclear, do you want the list of events with top 3 EventTotals in given year for every member who did parcipitate in this year, or SUM of those 3 for every member? Final query syntax depends on this.

  7. #7
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi Arvi
    Apologies for the slow response. Been away fighting fires. Thank you for your response. In reply to your questions;

    1. In your opening post you mentioned 'yearly competitions'. Does this mean, there are results from different years in ShootScores table? When yes, then you need a table tblEvents: EventID, EventDate, ..
    Yes, there are (or will be) results from previous years in the table. This is not an issue as I can filter the year that I want in the query.

    There are a number of other tables associated with this project (see image), but I don't think that matters for what I want from this particular query.

    I'm just looking for the top 4 scores across all events for the competitor, regardless of Event, though I can see that having top 4 scores across each event could be something in the future. That will be another headache as there are multiple divisions in which a member can compete. So I will have to decide if I go down that path later. For the moment I don't think that is necessary and I believe that the structure of the DB is such that I can achieve this in the future.

    In looking at your code, I don't see how this will give me the top 4 scores only.



    Click image for larger version. 

Name:	Capture.JPG 
Views:	32 
Size:	45.3 KB 
ID:	40393

  8. #8
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    UPDATE

    So after a lot of research, trial and error and swearing I seem to have a result that works. I "simplified" the process by working on a single total field (Score) rather than summing up a number of fields as in my original post.

    The code below gives me the top 3 scores for all competitors regardless of Event Type. At this time I am not looking at getting the top 3 results for each member across each individual event type. That's for the future

    The only problem with my solution is that, if a competitor competes in less that 3 events, then the query still shows their corresponding results. Ideally I'd like to have the query show me the top 3 scores for each competitor ONLY IF they have competed in 3 or more events. I'd be happy if someone could suggest the best way to achieve this. Maybe running another Query over this one

    Thanks again to those who have chimed in.

    Code:
    SELECT Members.[Last Name], Members.[First Name], ShootScores.Score
    FROM (Members INNER JOIN ShootScores ON Members.[Member ID] = ShootScores.[Member ID]) INNER JOIN [Event Type] ON ShootScores.Event = [Event Type].ID
    GROUP BY ShootScores.[Member ID], ShootScores.ScoresID, Members.[Last Name], Members.[First Name], ShootScores.Score
    HAVING (((ShootScores.ScoresID) In (SELECT TOP 3 ScoresID FROM ShootScores AS Dupe 
     WHERE Dupe.[Member ID] = ShootScores.[Member ID]
     ORDER By Dupe.Score DESC, Dupe.ScoresID DESC)))
    ORDER BY ShootScores.[Member ID], Members.[Last Name], ShootScores.Score DESC;

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You do now seem to have got the idea of the Top N per group query explained by Allen Browne

    To modify this to display the Top 3 results only for competitors who have at least 3 results, you should do that filter first.
    1. Create an aggregate (totals) query in which you group by members names (or better still by members ID) and count the number of scores.
    Filter for only those records where count>=3.

    2. Now create a new query joining that to the members table and use that instead for your top 3 query
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi Colin

    Thanks for the reply. I thought the same thing on my drive home last night.

    1. Create an aggregate (totals) query in which you group by members names (or better still by members ID) and count the number of scores.
    Filter for only those records where count>=3.

    2. Now create a new query joining that to the members table and use that instead for your top 3 query


    Creating the aggregate query and filtering where count >=3 will give me members who have competed in 3 or more events, but I still need to run the top 3 query after that to make sure that I get the top 3 highest scores for those who may have competed in more than 3 events.

  11. #11
    Join Date
    Apr 2017
    Posts
    1,673
    It's on fly of-course, but something like this must return top 4 scores for every member in every year for every bow type (you always can easily add or remove grouping levels). You can create a saved query like this (or like subquery s1), and use it as source of query with filter conditions later.
    Code:
    SELECT s.*
    FROM
     (
      SELECT
       s1.MemberID, 
       YEAR(s.DateOfShoot) AS ShootYear, 
       s1.CompetitorBowType,
       s1.Score
       (
        SELECT COUNT(s0.MemberID) 
        FROM ShootScores s0
        WHERE 
         s0.MemberID = s1.MemberID AND 
         YEAR(s0.DateOfShoot) = YEAR(s1.DateOfShoot) 
         AND s0.CompetitorBowType = s1.CompetitorBowType AND 
         s0.Score < s1.Score
       ) +1 AS ScoreRank
      FROM
       ShootScores s1
     ) AS s
    WHERE s.ScoreRank < 5
    NB! When e.g. 4. and 5. top scores for some group are equal, then both get same ScoreRank value (4), and top 5 scores are returned for this group instead of 4.

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by sheusz View Post
    Hi Colin

    Thanks for the reply. I thought the same thing on my drive home last night.

    1. Create an aggregate (totals) query in which you group by members names (or better still by members ID) and count the number of scores.
    Filter for only those records where count>=3.

    2. Now create a new query joining that to the members table and use that instead for your top 3 query


    Creating the aggregate query and filtering where count >=3 will give me members who have competed in 3 or more events, but I still need to run the top 3 query after that to make sure that I get the top 3 highest scores for those who may have competed in more than 3 events.
    Yes. Exactly what I meant. But the top 3 query is based on the filtered data rather than the whole table. Sorry if that wasn't clear originally.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi Colin and Arvil

    I managed to get the result I wanted by using a combination two queries.

    The first qry3orMoreEvents reads the table ShootScores and returns members who have competed in 3 or more events
    Code:
    SELECT Members.[Last Name], Members.[First Name], ShootScores.ScoresID, ShootScores.DateOfShoot, ShootScores.Event, ShootScores.Score, ShootScores.XScore
    FROM Members INNER JOIN ShootScores ON Members.[Member ID] = ShootScores.[Member ID]
    WHERE (((ShootScores.[Member ID]) In (SELECT [Member ID] FROM [ShootScores] As Tmp GROUP BY [Member ID] HAVING Count(*)>=3 )))
    ORDER BY ShootScores.[Member ID];
    I then incorporated this query as part of a second query Top3Scores which returns the highest 3 scores for each competitor who cmpeted in 3 or more events

    Click image for larger version. 

Name:	Capture.JPG 
Views:	13 
Size:	35.0 KB 
ID:	40461
    Code:
    SELECT Members.[Last Name], Members.[First Name], ShootScores.Score, [Event Type].[Event Type], ShootScores.DateOfShoot
    FROM ((Members INNER JOIN ShootScores ON Members.[Member ID] = ShootScores.[Member ID]) INNER JOIN [Event Type] ON ShootScores.Event = [Event Type].ID) INNER JOIN qry3orMoreEvents ON ShootScores.ScoresID = qry3orMoreEvents.ScoresID
    GROUP BY Members.[Last Name], Members.[First Name], ShootScores.Score, [Event Type].[Event Type], ShootScores.DateOfShoot, ShootScores.[Member ID], ShootScores.ScoresID
    HAVING (((ShootScores.ScoresID) In (SELECT TOP 3 ScoresID FROM ShootScores AS Dupe 
     WHERE Dupe.[Member ID] = ShootScores.[Member ID]
     ORDER By Dupe.Score DESC, Dupe.ScoresID DESC)))
    ORDER BY Members.[Last Name], Members.[First Name], ShootScores.[Member ID];
    On the dataset I am using this all seems to work nicely and returns the correct values.

    Now, ever the thinker, I'm wondering how I can have the query prompt me to enter the number of qualifying events, rather than hard coding the value 3. This means that if there is a change in the qualifying rules for the association, they can be catered for instead of having to modify the queries.

    Arvil, I like your idea and will have a play with it. Nice that it's all in one and may actually make the above question easier to answer.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Another query another problem - Top scores
    By binaarycode in forum Queries
    Replies: 4
    Last Post: 03-16-2019, 06:38 PM
  2. Trying to average two test scores using query
    By Ddempsii in forum Access
    Replies: 9
    Last Post: 11-04-2015, 02:01 PM
  3. Replies: 6
    Last Post: 08-27-2015, 11:35 PM
  4. Competitor Intelligence Database
    By cperry88 in forum Database Design
    Replies: 4
    Last Post: 11-11-2013, 03:46 PM
  5. Testing Scores
    By helpaccess in forum Queries
    Replies: 4
    Last Post: 08-22-2011, 09:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums