Where is Rank Function in Access 2007?

[SLK836]

I am trying to derive rank values in a query, just as one would with the Rank function in Excel. I use MS Access 2007 and want to build a query that will do it. I know how to build queries to give me only the top N but I don't want to have to build multiple queries for this request.

[twinnyfo]

SLK836,

As far as I know there is no Rank function (an incredible life saver in Excel!) in MS Access. However, what I have done is create a query. Then based on the results of that query, I create another query based on the same fields, and count how many records are above the current recordds based on the criteria of the current record.

For example, if you had a list of employees with their current salary, you would use the current record and count how many records in the current dataset have salaries greater than the current one. Then you add 1 (i.e. the highest salary will have zero records greater). This also accounts for ties, so that if record 3 and 4 are tied, they both receive 3's, but the next record is a 5.

Not sure if this makes sense, but without your data, and without writing your queries for you, this will get you in the right direction. I'll be glad to work through additioinal details....

[zmbd]

Two examples:
http://allenbrowne.com/ranking.html
http://support.microsoft.com/kb/120608

[Rabbit]

Subqueries are one way of doing it. They're slower but easier to understand.

You can also accomplish a similar result by joining the table to itself. It's faster but slightly more difficult to set up.

[zmbd]

SLK836:

Rabbit's suggestion of the self join would more than likely be the best solution.

Because you didn't post any data, I did not suggest the self join due to the potential difficulties in setting up the query and instead offered the other two methods.

I say, have a go at the self join and let us know how you fair... in either case (good or bad) please post your SQL so that we can either help or tune it!

Best of Luck