Assigning Rank and Points based on a field value 
Author Message
 Assigning Rank and Points based on a field value

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.

Any help would be appreciated.

Dale



Mon, 19 Jul 1999 03:00:00 GMT  
 Assigning Rank and Points based on a field value

Dale,
My first attempt would be an update query.  If the calculation you use to
update the Rank and Points is too complex for a query, then I would try a
function in the update query. If that didn't work, I would create a select
query or SQL statement and open it as a recordset. I would then loop
through the recordset updating the values inside the loop. An example:

Set Rs = Db.OpenRecordSet("QueryName")
If Rs.RecordCount > 0 then
   Rs.MoveFirst
   Do Until Rs.EOF
      Rs.Edit
         Rs.Rank = Some calculation
         Rs.Points = Some other calculation
      Rs.Update
   Loop
Rs.close

Hope this helps,
Scott Nation



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.

> Any help would be appreciated.

> Dale



Mon, 19 Jul 1999 03:00:00 GMT  
 Assigning Rank and Points based on a field value

Oops,
I made a common mistake in my last post.
Inside the loop, a line needs to be added:
     Rs.MoveNext
Otherwise you will have an infinite loop.
Scott Nation



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.

> Any help would be appreciated.

> Dale



Mon, 19 Jul 1999 03:00:00 GMT  
 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.



Sun, 25 Jul 1999 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Moving a field on a report, based on value of another field

2. Newbie - assigning calculated value to table field

3. Assigning new values to fields

4. Assign Value to Enterprise Project Field

5. Assign a string value to an enum field

6. Assign the null value to DB2 fields.

7. Assigning Null Values to data bound fields using remote data control

8. Assigning field values to variables

9. Counter Fields - value assigned on Addnew not Update?

10. Help assigning HEX value to 1 byte field in VB3.0

11. Assigning field values to variable

12. Assign a value to a formula field from VB Code

 

 
Powered by phpBB® Forum Software