none
Eliminate Select Query Duplicates RRS feed

  • Question

  • I am constructing an Access database for scoring a competition and am having a problem dealing with tied scores. Currently, I have a simple query that pulls the records containing the 25 highest scores (using the Top Value function). However, this query is unable to deal with ties for what is essentially 25th place. I figure this is because there is no provision for choosing between the two or three records, so the query simply returns them all, resulting in a list of 26 or 27 records, rather than 25.

    Historically, this has been done via Excel, and whoever was entering scores would simply choose one to move to the top 25. As such, I need some kind of function or code to isolate a tie for the 25th spot. I have been using an Rnd() function elsewhere in the database, so I can handle that part; I just don't know how to isolate records. Any help would be appreciated.


    • Edited by KyleCPA Wednesday, February 1, 2017 4:39 PM
    Wednesday, February 1, 2017 4:38 PM

All replies

  • Hi,

    Just a thought, you might use a function to assign a sequential number to the resulting records and then use another query to pull the first 25.

    Hope it helps...

    Wednesday, February 1, 2017 4:44 PM
  • What information are you using to break the tie?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, February 1, 2017 5:00 PM
  • You'll find examples of both row numbering and ranking queries in RowNumbering .zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes the following query which, with my current data (which might differ from that online) returns 14 rows which include three ties for 10th place:

    SELECT T1.TransactionDate, T1.TransactionAmount, T1.CustomerID,
    COUNT(T2.TransactionID)+1 AS Rank
    FROM Transactions AS T1 LEFT JOIN Transactions AS T2
    ON T2.TransactionAmount>T1.TransactionAmount
    GROUP BY T1.TransactionAmount, T1.TransactionDate,
    T1.CustomerID, T1.TransactionID
    ORDER BY T1.TransactionAmount DESC;

    If we amend it to return the TOP 10, and include the primary key TransactionID in the ORDER BY clause, then it returns 10 rows, eliminating the two ties with the higher TransactionID values

    SELECT TOP 10 T1.TransactionDate, T1.TransactionAmount, T1.CustomerID,
    COUNT(T2.TransactionID)+1 AS Rank
    FROM Transactions AS T1 LEFT JOIN Transactions AS T2
    ON T2.TransactionAmount>T1.TransactionAmount
    GROUP BY T1.TransactionAmount, T1.TransactionDate,
    T1.CustomerID, T1.TransactionID
    ORDER BY T1.TransactionAmount DESC, T1.TransactionID;

    Ken Sheridan, Stafford, England


    Wednesday, February 1, 2017 5:21 PM