none
Problem with Rank in Access Query RRS feed

Answers

  • Try this:

    SELECT T1.Level, T1.Sever, T1.Level*T1.Sever AS Material,
        (SELECT COUNT(*)+1
         FROM YourTable AS T2
         WHERE T2.Level*T2.Sever > T1.Level*T1.Sever) AS Ranking
    FROM YourTable AS T1
    ORDER BY T1.Level*T1.Sever DESC;

    Ken Sheridan, Stafford, England

    • Marked as answer by ryguy72 Tuesday, April 5, 2016 2:14 PM
    Monday, April 4, 2016 7:18 PM
  • See 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 examples of various methodologies for both ranking and
    numbering of a query's result table.  To break ties the key is brought into play.  The simplest way to number rows is of course to use the RunningSum property of an unbound text box control with a ControlSource property of =1.

    Ken Sheridan, Stafford, England

    • Marked as answer by ryguy72 Tuesday, April 5, 2016 2:14 PM
    Tuesday, April 5, 2016 8:25 AM

All replies

  • Try this:

    SELECT T1.Level, T1.Sever, T1.Level*T1.Sever AS Material,
        (SELECT COUNT(*)+1
         FROM YourTable AS T2
         WHERE T2.Level*T2.Sever > T1.Level*T1.Sever) AS Ranking
    FROM YourTable AS T1
    ORDER BY T1.Level*T1.Sever DESC;

    Ken Sheridan, Stafford, England

    • Marked as answer by ryguy72 Tuesday, April 5, 2016 2:14 PM
    Monday, April 4, 2016 7:18 PM
  • Thanks Ken!  That works pretty good, but if there is a tie, there is a problem.  As you can see in the image, the rank numbers stay the same but the counts are always increasing.

    This is called dense ranking.  Is there an easy way to make it ordinal ranking?  This may, literally, be a row count type of thing.  I tried to count the rows, but couldn't get anything working.  It's super-simple in SQL Server, but apparently quite a feat in Access.

    Any ideas?


    MY BOOK

    Monday, April 4, 2016 10:12 PM
  • See 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 examples of various methodologies for both ranking and
    numbering of a query's result table.  To break ties the key is brought into play.  The simplest way to number rows is of course to use the RunningSum property of an unbound text box control with a ControlSource property of =1.

    Ken Sheridan, Stafford, England

    • Marked as answer by ryguy72 Tuesday, April 5, 2016 2:14 PM
    Tuesday, April 5, 2016 8:25 AM
  • Thanks again!  The file looks great!!


    MY BOOK

    Tuesday, April 5, 2016 3:32 PM