# Thread: Rank or Row Number (Access 2000 (9.0.3821 SR-1))

1. ## Rank or Row Number (Access 2000 (9.0.3821 SR-1))

I have a query with various calculations returning 265 rows sorted by a calculated field.

The answer (sorted ascending) is actually ranking of rows from 1-265. I need to have a separate column in the query that returns either the ranking in order or simply the row number which will in essence be the same as the rank.

In a report I can do this with an =1 in a query I have no clue.

2. ## Re: Rank or Row Number (Access 2000 (9.0.3821 SR-1))

<A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=96095&page=& view=&sb=&o=&vc=1#Post96095>This should do what you want, or at least point you in the right direction.
</A>

3. ## Re: Rank or Row Number (Access 2000 (9.0.3821 SR-1))

Perfection!
I am sure this is the answer!
Happy New Year! <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

4. ## Re: Rank or Row Number (Access 2000 (9.0.3821 SR-1))

I can not believe I am dusting this one off after almost a year but I have a new issue...

The solution was offered and it was perfect for a long time. Now I am in a new Db and using the EXACT format for the ranking and I am getting the higest number repeated in all rows.

REMINDER: I have a calculated field and I need to add a sequence number indicating the ranking from highest to lowest.
Seq: (select count(*) from [1Prod] S where S.[ProdRankScore] >= S.[ProdrankScore] )

[1Prod] is the table
[ProdRankScore] is the field to be ranked

How could this work perfectly in one Db and not in another???

Any thoughts from the Woody's Lounge Gods will be greatly appreciated!

5. ## Re: Rank or Row Number (Access 2000 (9.0.3821 SR-1))

I would use :
Seq: DCount("ProdRankScore","1Prod","[ProdRankScore] >= " & [ProdrankScore])

6. ## Re: Rank or Row Number (Access 2000 (9.0.3821 SR-1))

In

Seq: (Select Count(*) From [1Prod] S Where S.[ProdRankScore] >= S.[ProdRankScore])

you use S.[ProdRankScore] twice - this makes the condition True for all records, so it will always result in the total number of records. You should replace the first S.[ProdRankScore] by 1Prod.[ProdRankScore]:

Seq: (Select Count(*) From [1Prod] S Where 1Prod.[ProdRankScore] >= S.[ProdRankScore])

(Francois' suggestion of using DCount should work too.)

7. ## Re: Rank or Row Number (Access 2000 (9.0.3821 SR-1))

That makes sense... and works perfectly

Thank you for saving my sanity. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•