rank and update query

[Guest]

I have an investment file named Fund that has the following fields: Fund#,
Name, %Return1, %Return2, Rank1, and Rank2. I want to update Rank1 with a
sequential number starting with 1, based upon the descending value in
%Return1 and the same logic for %Return2.

# Name %1 %2 Rank1 Rank2
1 ABC 10 25 2 3
2 XYZ 9 38 3 1
3 PDQ 12 30 1 2

Any ideas?

 

[Michel Walsh]

Hi,


SELECT a.name,
a.return1,
(SELECT COUNT(*) FROM tableName As b WHERE b.return1>=a.return1) As
Rank1,
a.return2,
(SELECT COUNT(*) FROM tableName As b WHERE b.return2>=a.return2) As
Rank2
FROM tableName As b



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks. That did the trick.

Hi,


SELECT a.name,
a.return1,
(SELECT COUNT(*) FROM tableName As b WHERE b.return1>=a.return1) As
Rank1,
a.return2,
(SELECT COUNT(*) FROM tableName As b WHERE b.return2>=a.return2) As
Rank2
FROM tableName As b



Hoping it may help,
Vanderghast, Access MVP