Question

[KAJ7481]

Say I have a table of information. As an easy example, the first column is names, there may be duplicates. Teh second column is a monetary amount. I want Access to add a third column, ranking the monetary amounts for each person. For example, if there are three entries for Joe Smith, one with $10,000, the second with $5,000, and the thrid with $7,500, I want Access to place a 1 next to the highest, and down to a 3 for the lowest. Access should do the same thing for each person. (So numbers will repeat, it is like ranking each person seperate from one another). Any advice?

 

[PHV]

SELECT A.person, A.amount, (SELECT COUNT(*) FROM yourTable B WHERE B.person=A.person AND B.amount>=A.amount) AS Rank
FROM yourTable A
ORDER BY 1, 3

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KAJ7481]

PHV,

Thanks for your help. Actually, I saw your answer regarding the same thing and have already tried the query that you just posted. It worked great when i used it for a small table. I am now trying to run it for a database with over 20,000 records and it takes forever to run. Is there any other way to accomplish the same thing?

 

[PHV]

SELECT A.person, A.amount, Count(*) AS Rank
FROM yourTable A INNER JOIN yourTable B
ON A.person=B.person AND A.amount<=B.amount
GROUP BY A.person, A.amount;

You may at least create an index (dup allowed) on person
or even a composite index on (person,amount).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244