update field by group rank

[Guest]

I have a table with a firm-id field and rank field. I want to update the
rank field in the table with a sequence number from 1 to whatever for each
firm-id group.

now:

firm-id rank
1
1
1
2
2
3

want:

firm-id rank
1 1
1 2
1 3
2 1
2 2
3 1

table name is master. all help appreciated!

 

[Michel Walsh]

Hi,


You either need a loop, to produce multiple small batches of update,
producing multiple updates of one record at a time, such as using a
recordset, either to use temporary table. With a recordset, COUNT the
number of records with the same firm_id and having a rank that is not null:

Do Until myRecordset.EOF
myRecordset.Edit
myRecordset("rank") = DCount("rank", "myTable", "firm_id=" &
mtRecordset("firm_id")
myRecordset.Update
myRecordset.MoveNext
Loop



Using a temporary table: first append all the data in a temp table
having an autonumber:

CREATE TABLE temp (f1 COUNTER, firmID LONG)


and append the data into it


INSERT INTO temp(firmID) SELECT firm_ID from myTable


then, you can rank, with ONE statement, since the autonumber allows us
to differentiate between each record.


SELECT a.firmID, COUNT(*) As rank
FROM temp As a INNER JOIN temp As b
ON a.tempID=b.tempID AND a.f1 >= b.f1
GROUP BY a.firmID



or your favorite SQL expression to compute the rank (which should be a
native construction in the next MS SQL Server version).


Hoping it may help,
Vanderghast, Access MVP