Ranking records

[Alen]

Hi,
I need help in VBA code to rank the records in a table which has 2
columns: RandomKey and "FS at 100%". There might be duplicates
RandomKey with different "FS at 100%" amount. I want to add a Rank
column to the table to help me rank a particular RandomKey with 8
amount number with rank order 1, 2, 3, etc from the largest number in
the "FS at 100%". Below is a sample of my data:

RandomKey FS at 100%
RNK17290704 96.62
RNK17290712 57.31
RNK17290715 111.84
RNK17290715 111.84
RNK17290720 96.62
RNK17290720 96.62
RNK17290721 75.95
RNK17290734 96.62
RNK17290749 75.95
RNK17290749 75.95
RNK17290753 96.62
RNK17290757 75.95
RNK17290769 96.62
RNK17290787 75.95
RNK17290790 75.95
RNK17290790 4.96
RNK17290792 111.84
RNK17290792 111.84
RNK17290792 111.84
RNK17290792 111.84
RNK17290792 21.68
RNK17290792 21.68
RNK17290792 21.68
RNK17290792 21.68
The desired output I want is as followed:

RandomKey FS at 100% Ranking
RNK17290704 96.62 1
RNK17290712 57.31 1
RNK17290715 111.84 1
RNK17290715 111.84 2
RNK17290720 96.62 1
RNK17290720 96.62 2
RNK17290721 75.95 1
RNK17290734 96.62 1
RNK17290749 75.95 1
RNK17290749 75.95 2
RNK17290753 96.62 1
RNK17290757 75.95 1
RNK17290769 96.62 1
RNK17290787 75.95 1
RNK17290790 75.95 1
RNK17290790 4.96 2
RNK17290792 111.84 1
RNK17290792 111.84 2
RNK17290792 111.84 3
RNK17290792 111.84 4
RNK17290792 21.68 5
RNK17290792 21.68 6
RNK17290792 21.68 7
RNK17290792 21.68 8

Thank you very much for all help,

 

[John W. Vinson]

Hi,
I need help in VBA code to rank the records in a table which has 2
columns: RandomKey and "FS at 100%". There might be duplicates
RandomKey with different "FS at 100%" amount. I want to add a Rank
column to the table to help me rank a particular RandomKey with 8
amount number with rank order 1, 2, 3, etc from the largest number in
the "FS at 100%". Below is a sample of my data:

Given that you have records which are exactly identical, this will be tricky:
you need *something* to break the tie to assign different ranks to identical
records. Are there any other fields in the table? or could you add an
Autonumber field?

The ranking query could be something like

SELECT [RandomKey], [FS at 100%], (SELECT Count(*) FROM tablename AS R WHERE
R.RandomKey = TableName.RandomKey AND R.[FS at 100%] <= tablename.[FS at 100%]
AND R.tiebreaker <= tablename.tiebreaker) AS Ranking;
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com