By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,391 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 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
Share this Question
Share on Google+
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" <chrislab...@gmail.comwrote:
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 #2

P: n/a
On Mar 6, 4:32 pm, "ManningFan" <manning...@gmail.comwrote:
I don't get it. Why not just use an Order By statement?

On Mar 6, 4:25 pm, "Chris" <chrislab...@gmail.comwrote:
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.- Hide quoted text -

- Show quoted text -
I want ties to show up blank when ranking

Mar 6 '07 #3

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" <chrislab...@gmail.comwrote:
>
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.