How do I rank numbers by using Sql

[Helge's]

I have a table like this:

Numbers
10
20
30
40

How can I make a sql in Access? I want it like this:

Numbers Rank
10 4
20 3
30 2
40 1

[Allen Browne]

Type something like this into the Field row in query design:
Rank: (SELECT Count(*)
FROM Table1 AS Dupe
WHERE Dupe.Numbers <= Table1.Numbers)

It's one line.
Substitute your table name for Table1.
Results will be read-only.

Alternative that is slower but not read-only:
DCount("*", "Table1", "Numbers <= " & [Numbers])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Helge's" <he*************@chello.nowrote in message
news:11*********************@75g2000cwc.googlegrou ps.com...

>I have a table like this:

Numbers
10
20
30
40

How can I make a sql in Access? I want it like this:

Numbers Rank
10 4
20 3
30 2
40 1