tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment

Other Sites

CheckYourMath BigActivities DigMinecraft
Share this page:

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

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

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)))))

Microsoft Access

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