---

MS Access 2003: Perform a stack rank in a query

This MSAccess tutorial explains how to perform a stack rank in a query in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, is there a way to write a formula in a query that would stack rank a group of numbers in a field? I have a table called "Data Table" with a field named "AHT" that contains whole numbers like 245, 352, and 536.

I'd like the Access query to evaluate the number in the AHT field as follows:

If AHT is in the top 20%, return a 1.
If AHT is in the next 20%, return a 2.
If AHT is in the next 20%, return a 3.
If AHT is in the next 20%, return a 4.
Then the remaining numbers, return a 5.

Is this possible?

Answer: Yes, this can be done by using the DCount function and IIf function as follows:

IIf(DCount("AHT","Data Table","AHT<=" & [AHT])/DCount("AHT","Data Table")<=0.2, 1, IIf(DCount("AHT","Data Table","AHT<=" & [AHT])/DCount("AHT","Data Table")<=0.4, 2, IIf(DCount("AHT","Data Table","AHT<=" & [AHT])/DCount("AHT","Data Table")<=0.6, 3, IIf(DCount("AHT","Data Table","AHT<=" & [AHT])/DCount("AHT","Data Table")<=0.8, 4, 5))))

Please note that this method of evaluating Stack Rank will give duplicate numbers the same rank.