Assigning Rank and Points based on a field value
Dale,
Try using a procedure like the one that follows. In order to use this code
you need a table called "Table1" with fields called "Value", "Rank", and
"Points". Of course you can rename any of these, however you need to
change the names in the code as well.
The procedure sorts your records by Value in descending order (largest
value first). It then moves from the first record to the last inserting
the rank (from 1 to however many records you have), and calculating the
"Points". I don't know what formula you use to calculate points but all
you need to do is replace the formula I've used (Value divided by 10) with
your own.
Give it a shot and let me know if you need any help.
Todd Penland
Sub RankValues()
Dim dbCurrent As DATABASE
Dim rsRst1 As Recordset
Dim iRank As Integer
Dim stSQL As String
' ******************************************************************
' The "& _" in each of the following lines is included to
' make the SQL easier to read. You can remove these if
' you want.
' ******************************************************************
stSQL = "SELECT " & _
"Value, " & _
"Rank, " & _
"Points " & _
"FROM Table1 " & _
"ORDER BY Value DESC;"
' ******************************************************************
' Create a recordset containing the results of stSQL
' ******************************************************************
Set dbCurrent = CurrentDb
Set rsRst1 = dbCurrent.OpenRecordset(stSQL)
' ******************************************************************
' Set your rank value to 1
' ******************************************************************
iRank = 1
' ******************************************************************
' Move to the first record in the recordset (because of
' the sort order, this will be the record with the highest
' value.
' ******************************************************************
rsRst1.MoveFirst
' ******************************************************************
' Execute the following loop until you reach the last
' record in the recordset.
' ******************************************************************
Do Until rsRst1.EOF
rsRst1.Edit
' ******************************************************************
' Insert the value of iRank into the Rank field
' ******************************************************************
rsRst1!Rank = iRank
' ******************************************************************
' Calculate your "points" using whatever formula you have
' for this value and insert that value into the Points
' field. In this example, I'll use Value divided by 10
' ******************************************************************
rsRst1!Points = rsRst1!Value / 10
' ******************************************************************
' Save the change you have made to the current record.
' ******************************************************************
rsRst1.UPDATE
' ******************************************************************
' Increment iRank by 1 and move to the next record. Repeat.
' ******************************************************************
iRank = iRank + 1
rsRst1.MoveNext
Loop
' ******************************************************************
' Close the recordset and the database
' ******************************************************************
rsRst1.Close
dbCurrent.Close
End Sub
Quote:
> I have three fields Value, Rank, and Points. The Value has been computed
> from another table and I now want to update the Rank and Points based on
> the Value (descending).
> In dbase I would set the order, go to the top of the file and run through
> the records until EOF. How would I do this using VBA.