435,619 Members | 1,861 Online
Need help? Post your question and get tips & solutions from a community of 435,619 IT Pros & Developers. It's quick & easy.

# Ranking numbers

 P: n/a I was wodering if there was a way to rank numbers in a query like this #'s Rank 100 1 99 2 98 3 98 97 5 96 6 96 95 8 And also I would need th abilty to change it from ranking highest to lowest to ranking lowest to highest. Mar 6 '07 #1
5 Replies

 P: n/a I don't get it. Why not just use an Order By statement? On Mar 6, 4:25 pm, "Chris"

 P: n/a On Mar 6, 4:32 pm, "ManningFan"

 P: n/a Ahhh... I see now. You're going to have to write code to do that. The first thing off the top of my head would be to run an Order By query, write the results into a multi-dimensional array and test each value as you're writing it. If (X,1) = (X-1,1) then (X, 2) = " ", otherwise (X, 2) = (X-1, 2) - 1. It's pretty clumsy though. On Mar 6, 4:35 pm, "Chris" I want ties to show up blank when ranking- Hide quoted text - Mar 6 '07 #4

 P: n/a Chris wrote: I was wodering if there was a way to rank numbers in a query like this #'s Rank 100 1 99 2 98 3 98 97 5 96 6 96 95 8 And also I would need th abilty to change it from ranking highest to lowest to ranking lowest to highest. To semi-duplicate your problem I did the following. I created a table called Table1 with 2 fields; ID (autonumber) and NumV (Number). To this table, in NumV, I entered the following numbers; 1,2,2,3,3,3,4,5,6. I then created a query (Query2) that would display only unique numbers from Table1. This will help in determining the ranking. The SQL was SELECT DISTINCT Table1.NumV FROM Table1 ORDER BY Table1.NumV; I now created the query (Query1) to display data like you did. You might need to sort it differently and modify the < comparisons in the expressions to get your desired results. Maybe not. I created 4 expressions (calced columns). Each expression shows the individual steps to create the resulting column Expr4 that is the rankings with duplicate blanks. The SQL for Query1 is SELECT Table1.NumV, DCount("NumV","Query2","NumV < " & [NumV])+1 AS Expr1, DCount("NumV","Table1","NumV = " & [NumV]) AS Expr2, DMin("ID","Table1","NumV = " & [NumV])=[ID] AS Expr3, IIf(DCount("NumV","Table1","NumV = " & [NumV])=1 Or DMin("ID","Table1","NumV = " & [NumV])=[ID],CStr(DCount("NumV","Query2","NumV < " & [NumV])+1)," ") AS Expr4 FROM Table1; OK, if you create the Table, enter several records, create two queries Query1 and Query2 and run Query1, you'll see how it all works. My results running Query1 are 1,2, ,3, , ,4,5,6 using the values I entered. Mar 7 '07 #5

 P: n/a salad wrote: Since the results column (Expr4) is comprised of Expr1..Expr3, you only need NumV and then sort on Expr1. SELECT Table1.NumV, IIf(DCount("NumV","Table1","NumV = " & [NumV])=1 Or DMin("ID","Table1","NumV = " & [NumV])=[ID],CStr(DCount("NumV","Query2","NumV < " & [NumV])+1)," ") AS Expr4 FROM Table1 ORDER BY DCount("NumV","Query2","NumV < " & [NumV])+1; Mar 7 '07 #6

### This discussion thread is closed

Replies have been disabled for this discussion.