Results 1 to 2 of 2
  1. #1
    Marvo is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    May 2015
    Posts
    4

    Complex Grouping and Sorting - custom sorts within groups - HELP!

    Thanks all in advance for your genius....



    I have an application that records scores in golf competitions and I use a report to publish the competition results.
    Some competitions may be played over more than one round of golf. Let's assume for simplicity that competitors complete 2 rounds of golf.
    The winner is the player with the highest aggregate points score.
    Often, particularly in a large field, players may have equal scores over the two rounds. There is a method used (called 'countback') to determine the placing of these players.


    I would like to achieve the following result in my report layout:
    =========================================
    Place Name Total Score

    1 Player A 85
    Round 1 score 40
    Round 2 score 45

    2 Player B 81
    Round 1 score 36
    Round 2 score 45

    3 Player C 81
    Round 1 score 39
    Round 2 score 42

    4 Player D 81
    Round 1 score 41
    Round 2 score 40

    =========================================


    Easy right? So - Player A wins. No problem.
    The reason that I want to display the Round 1 and Round 2 scores as well as the total is a) it looks nice b) it is crucial to determining the 'countback' or tie breaker score.

    Players B, C and D all had an aggregate score of 81 and are tied
    Places are then determined by who had the best score in Round 2. In this case, Player B.
    If players are still tied at this point, then further calculations are made - which I can manage later.

    I just can't get the grouping and sorting worked out...

    I have to group by player - so that I can show their 2 rounds together
    I have to sort (or group?) by total score so that total scores are ordered best to worst

    AND/BUT the player groups must then be ordered by which player has the best Round 2 score! (as well as by total score)

    Makes sense in my brain but can't get Access to understand! HELP!!
    Many thanks


    ps - I can get it to work if I ONLY show Round 2, but not when I show Round 1 and Round 2 and have them in order - it all breaks

    Data for the report is coming from a Query - I've tried to do sorting in the query and the report

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Reports ignore query sorting so don't even bother with the query. Not sure if you can group by sum descending over a group by players and within that, sort by round 2.
    You might need a hidden calculated textbox in the report to sum rounds and sort by that (verify that the proper player keeps the association with the sum). Then apply another sort based on round 2/player combination. If that works I'll be surprised since designing reports in my head is not something I like to do (not real good at it). However, if I have one to work with I usually win.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Custom Grouping & Sorting (pics attached)
    By Eirini_kap in forum Reports
    Replies: 8
    Last Post: 02-03-2016, 11:00 AM
  2. Replies: 3
    Last Post: 03-26-2015, 11:10 AM
  3. Complex Sorts in Datasheet View
    By Paul H in forum Forms
    Replies: 4
    Last Post: 05-09-2014, 10:10 AM
  4. Grouping, then counting groups.
    By RedWolf3x in forum Queries
    Replies: 6
    Last Post: 05-31-2012, 09:52 AM
  5. Replies: 11
    Last Post: 01-12-2012, 07:55 PM

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