---

MS Access 2003: Perform a stack rank (values greater than 0) in a query

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

Question: In Microsoft Access 2003/XP/2000/97, you helped me write a stack rank formula with the logic below:

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.

It works great except that I have a few AHT values that are 0 and I do not want those values to be added into the stack ranking. Is there a way to write the formula so that if the value is >0, then stack rank?

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

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

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