Automatic entry based on a relative value (1 Viewer)

[rt49]

I'm using Access 2000. How do I use the relative value of each record in one field to automatically enter a value for each corresponding record in another field?

For example, members of a sales force accumulate points for Regular Sales, Bonus Sales, and Training Others. These are three number fields (each field size is decimal and is limited with a validation rule) on a table called Points. The points are then totaled in a field called Total Points in a query called Awards. The Total Points field is sorted descending.

At the end of each month, an award called Gold is given to the top two members of the force (based on point total), Silver to the next two members, and bronze to the fifth and sixth place members. This information (the words Gold, Silver, and Bronze) is automatically entered in the winning members' records on a field called Award on the Awards query. The words No Award are automatically entered on the other members' records, or nothing is entered. What is the method for causing these automatic entries? Thanks for any help.

 

[Ken Grubb]

In a Query? I don't see this happening. However, in a Module, and if you store Award in your Points table, it's doable.

First, you'll need to do an update (in code) of the Award field in your Points table to Null out last month's Award values. The code below will return the top 6 performers from the Points table. AbsolutePosition in the recordset will allow you to assign Gold, Silver, and Bronze.

For simplicity, you probably then wanna store the SalesmanID and Award in an array and Set rst = Nothing.

Finally, use the array to update this months Gold, Silver and Bronze winners in the Points table.

-----------------------

Dim rst As Recordset
Dim strSQL As String
strSQL = "SELECT TOP 6 [Points].[SalesmanID], " & _
"[RegularSales]+[BonusSales]+[TrainingOthers] AS TotalSales " & _
"FROM Points ORDER BY [RegularSales]+[BonusSales]+[TrainingOthers] DESC;"
Set rst = strSQL

With rst
.MoveFirst
Do While Not .EOF
Select Case .AbsolutePosition
Case 1 To 2
' Gold Award
Case 3 To 4
' Silver Award
Case 5 To 6
' Bronze Award
End Select
.MoveNext
Loop
End With