Ranking Data in SQL query

[snowb1sp19]

Hello,

I have searched and still can't quite get my query to work correctly. I need to assign a ranking in an Access query. I have a table with the following fields:

Key - (represents a name for a manufacturing location)
Rate - (represents a cost to move product from this location)
Tier - (This is where I hope the rank to be)

The Key will have multiple rates, for example:
Key......Rate......Tier
CA05US8Z.....500....1
CA05US8Z.....600.....2
CA05US8Z.....700.....3
CA05US8Z.....800.....4

I want to write a query that will rank the data (which would be saved in the Tier field).

So if I add a filed to the table that is:
Key.....Rate


CA05US8Z.....550
I would want that to bump the other Tier ranks down when I run the query.

Does anyone know how the SQL would look to write this query?

Thanks,

[ssanfu]

Have you tried Googling "Ranking in SQL"?
the first 4 hits are:

http://www.1keydata.com/sql/sql-rank.html
http://msdn.microsoft.com/en-us/library/ms189798.aspx
http://msdn.microsoft.com/en-us/library/ms176102.aspx
http://blog.sqlauthority.com/2007/10...se_rank-ntile/

[snowb1sp19]

I have searched, but none of them apply directly to my situation. The first one is ranking the data as though there is a primary key and I don't have that. The first link gives unique names, however my data has multiple names, but different rates. So for example:

Key......Rate......Tier
CA05US8Z.....500....1
CA05US8Z.....600.....2
CA05US8Z.....700.....3
CA05US8Z.....800.....4
US01USFD.....500.....1
US01USFD.....600.....2
US01USFD.....800....3

I have multiple Keys, and its the rate i want to rank.

Any help is appreciated,

Kevin