Ranking (Correct Order)

[BMac via AccessMonster.com]

Hey everyone, I have read TONS of questions on this issue but I can not seem
to cut through them to find the one I need. I am sure its out there, so I
am sorry if I am restating something already out there.

I am looking to simply rank within MS Access. I used the DCount approach but
it does not give me the correct ranking I need.

For instance:

Table Name: tbl_Sales

Name Sales
Joe 15
Brad 20
Kerry 15
Bob 5
Hairy 20

DCount gives me this:

Name Sales Rank
Joe 15 3
Brad 20 5
Kerry 15 3
Bob 5 1
Hairy 20 5

What I need is:
Name Sales Rank
Joe 15 2
Brad 20 4
Kerry 15 2
Bob 5 1
Hairy 20 4

The main reason for this, is tha the users then want to have a calculation
off that ranks from about 60 other ranks, someone who is scored a 5 instead
of a 4 because of a duplicate messes up those sumed rankings. In some cases
the ranking is hitting just four numbers, which causes an even greater number
descrepency. In Excel it does it correctly, but I can not seem to duplicate
it with SQL or with MS Access. I would prefer to house everything in Access
instead of move it into Excel and then back into Access.

As a note, I found one method that utilized three seperate querries to
calculate that one field, the problem I have with that is I need to duplicate
this over 60 times.

Is there something out there that will do that? I really appreciate any help
you can give me.

 

[KARL DEWEY]

Try this --
SELECT Q.[Name], Q.[Sales], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Sales] < Q.[Sales])+1 AS Rank
FROM Product AS Q
ORDER BY Q.[Name], Q.[Sales] DESC;

 

[Michel Walsh]

Here a solution using a join:

SELECT a.name, LAST(a.sales), COUNT(b.sales)+1 AS rank
FROM tableName AS a LEFT JOIN tableName As B
ON a.sales < b.sales
GROUP BY a.name



Vanderghast, Access MVP

 

[BMac via AccessMonster.com]

That seemed to work.. Thank you so much!

Try this --
SELECT Q.[Name], Q.[Sales], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Sales] < Q.[Sales])+1 AS Rank
FROM Product AS Q
ORDER BY Q.[Name], Q.[Sales] DESC;

Hey everyone, I have read TONS of questions on this issue but I can not seem
to cut through them to find the one I need. I am sure its out there, so I

[quoted text clipped - 45 lines]

Is there something out there that will do that? I really appreciate any help
you can give me.

 

[Michel Walsh]

Sorry, my solution does NOT work in general. I was thinking to something
else. You should use the sub-query method instead.



Vanderghast, Access MVP