Ranking Prices 2

[KimVong]

I have a query and I would like to insert a field to this query that will give me a ranking from the highest price to the lowest, can anyone help me?

table is as follow

desc price ranking
A 10 1
B 8 2
C 5 3

How can I insert the ranking field into this table so that it will automatically ranking my prices.

any help is greatly appreciate

 

[OU18]

PHV, Yeah the code is

Function GetRank()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select * from tblSteve")
Dim i As Integer
i = 1
While Not (rs.EOF)
rs.Edit
rs!rank = i
rs.Update

rs.MoveNext
i = i + 1
Wend
rs.Close
End Function

There are actually two fields that would determine each group

3Digit lata
OCN

For each group of 3 Digit Lata and OCN there can be up to 10 unique routes. So what I need is for it to Start over at 1 for each new group of 3 digit lata and OCN.

Thanks

 

[PHV]

Something like this ?
Function GetRank()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select * from tblSteve ORDER BY [3Digit lata],OCN,Price")
Dim i As Integer, dig, ocn
i = 1
If Not (rs.BOF Or rs.EOF) Then
dig = rs![3Digit lata]: ocn=rs![OCN]
End If
While Not (rs.EOF)
If dig <> rs![3Digit lata] Or ocn <> rs![OCN] Then
i = 1: dig = rs![3Digit lata]: ocn=rs![OCN]
End If
rs.Edit
rs!rank = i
rs.Update
rs.MoveNext
i = i + 1
Wend
rs.Close
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OU18]

PHV,
This more then helped. You just saved me a whole lot of going between programs.

Have a star