Record Ranking by RecordNumber? Please Help

[sjohnson77]

Is there a way to update a field in a sorted query with its recordnumber value, as if to assign a rank to it?

Example Table contains Name, Grade, Rank sorted by Grade ASC

Record 1: John, A, *Update here*
Record 2: Tony, C, *Update here*
Record 3: Eric, D, *Update here*

The *Update here* is the bit that I need to update with each records recordnumber, so that the end result would be as follows:

Record 1: John, A, 1
Record 2: Tony, C, 2
Record 3: Eric, D, 3

Can anyone please help?

Thank,

Steve Johnson

 

[Pat Hartman]

A little searching would have turned up these posts:
http://www.access-programmers.co.uk/forums/showthread.php?t=79157&highlight=rank
http://www.access-programmers.co.uk/forums/showthread.php?t=5873&highlight=ranking

 

[sjohnson77]

Sorry, Pat, I do apologize. I'm new to the forum and did do a couple searches, but in the wrong categories, so I found no results.

I found some help from MS last night just before I left the office and was able to successfully implement it this morning.

Much thanks,

Steve Johnson

BTW - Here is the source I found.

http://support.microsoft.com/?kbid=208946

 

[Pat Hartman]

That's a good article. But if you examine the technique, you will see that it is extremely expensive since to obtain the rank, a new query must be run for EVERY row in your recordset. So, if your recordset contains only 100 rows, 100 queries must be run. Unfortunately, there is no good way to rank records in a query. However, there is a very simple and efficient way to rank records in a report.

Add a new control
Set its ControlSource to
=1
Set its runningsum property to overAll or OverGroup which ever works for your needs.