Problem with Rank in Access Query

Question

I think my Access skills and SQL skills are ok, but my Ranking skills are not on par.  I have an image that looks like this.

This is a very simple calculation.  Material = Level * Sever.  I'm now trying to Rank order the 'Material' calculated field.  Any suggestions as to how to do this?  I've done some Google searching and tried a couple of things.  I keep getting '1' for everything.  That's definitely not right.  I'd like to do some kind of count, and add 1 to the previous record and sort by the 'Material' field.  How can I do that?

---

Check out my book!!!

http://www.amazon.com/Automating-Business-Processes-Reducing-Increasing-ebook/dp/B01DJJKVZC/ref=sr_1_1?ie=UTF8&qid=1459738423&sr=8-1&keywords=ryan+shuell

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

Thanks again!  The file looks great!!

---

MY BOOK