Sorting and scoring 2

[Manowar]

I would like to create a sorting and scoring sheme in Access across a number of fields in one table. If necessary, generating another table from the query.

Table has a number of categories for employees. Each category has a number associated with it.

I want Access to sort then score each category for each person IE:

NAME CALLS SPEED TIME
Joe 100 54 20
Mary 50 17 13
Jane 75 33 15

Would be
CALL SPEED TIME
NAME CALLS SCORE SPEED SCORE TIME SCORE TOTAL
Joe 100 3 54 1 20 1 5
Mary 50 1 17 3 13 3 7
Jane 75 2 33 2 15 2 6

Best Mary 7
Jane 6
Joe 5

 

[PaulF]

remove the End If between .Update and .MoveNext

PaulF

 

[Manowar]

Nope ... Still dings

 

[Manowar]

CODE NOW

With rst
.MoveFirst
intValue = rst!ACDCalls
intLastCount = 1
Do While Not .EOF
If rst!ACDCalls = intValue Then
rst!SCOREACDCalls = intLastCount
Else
rst!SCOREACDCalls = rst.AbsolutePosition + 1
intValue = rst!ACDCalls
intLastCount = rst.AbsolutePosition + 1
End If
.Update
.MoveNext
Loop
End With

 

[PaulF]

Please list the 6 field names, field types, and sample data for each, and I'll try to reconstruct it on my Pc

PaulF

 

[Manowar]

Field names

ACDCalls (Round numbers from 1 to ~ 400)
RONA (Round numbers from 0 to ~ 30)
AvgACDTimeMin (2 decimal place numbers from ~2 to ~ 30)
AvgACWTimeSec (2 decimal place numbers from ~2 to ~ 30)
PercentAvail (%)
ACDCallsPerAvailHour (2 decimal place numbers from ~.5 to ~ 4)

All are NUMBER - DOUBLE
First 2 are AUTO decimal ... Ones that need 2 are 2
Percent is %

Scoring field names are the same with SCORE in front

 

[PaulF]

Ok, here you go, Problem was, there wasn't an .Edit command when you were trying to Update the Scores. Now I ran this and it seemed to run OK. You "STILL NEED TO" add the Desc command for those fields that you want sorted in Descending order. I'd leave the code as is (tests all six fields for ties, instead of just the three). One other thing to check is that you have identical Field Types and Formats between the two tables for the six fields.. HTH PaulF

Public Function ReturnScore()
DoCmd.SetWarnings False
DoCmd.RunSQL ("Delete * From tblTempSortingAndScoring;&quot
DoCmd.RunSQL ("INSERT INTO tblTempSortingAndScoring SELECT" _
& " tblSortingAndScoring.* FROM tblSortingAndScoring;&quot
DoCmd.SetWarnings True
Dim db As DAO.DATABASE, rst As DAO.Recordset
Dim intValue As Variant, intLastCount As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From tblTempSortingAndScoring Order By ACDCalls;&quot
With rst
.MoveFirst
intValue = rst!ACDCalls
intLastCount = 1
Do While Not .EOF
.Edit
If rst!ACDCalls = intValue Then
rst!SCOREACDCalls = intLastCount
Else
rst!SCOREACDCalls = rst.AbsolutePosition + 1
intValue = rst!ACDCalls
intLastCount = rst.AbsolutePosition + 1
End If
.Update
.MoveNext
Loop
End With
Set rst = db.OpenRecordset("Select * From tblTempSortingAndScoring Order By RONA;&quot
With rst
.MoveFirst
intValue = rst!RONA
intLastCount = 1
Do While Not .EOF
.Edit
If rst!RONA = intValue Then
rst!SCORERONA = intLastCount
Else
rst!SCORERONA = rst.AbsolutePosition + 1
intValue = rst!RONA
intLastCount = rst.AbsolutePosition + 1
End If
.Update
.MoveNext
Loop
End With
Set rst = db.OpenRecordset("Select * From tblTempSortingAndScoring Order By AvgACDTimeMin;&quot
With rst
.MoveFirst
intValue = rst!AvgACDTimeMin
intLastCount = 1
Do While Not .EOF
.Edit
If rst!AvgACDTimeMin = intValue Then
rst!SCOREAvgACDTimeMin = intLastCount
Else
rst!SCOREAvgACDTimeMin = rst.AbsolutePosition + 1
intValue = rst!AvgACDTimeMin
intLastCount = rst.AbsolutePosition + 1
End If
.Update
.MoveNext
Loop
End With
Set rst = db.OpenRecordset("Select * From tblTempSortingAndScoring Order By AvgACWTimeSec;&quot
With rst
.MoveFirst
intValue = rst!AvgACWTimeSec
intLastCount = 1
Do While Not .EOF
.Edit
If rst!AvgACWTimeSec = intValue Then
rst!SCOREAvgACWTimeSec = intLastCount
Else
rst!SCOREAvgACWTimeSec = rst.AbsolutePosition + 1
intValue = rst!AvgACWTimeSec
intLastCount = rst.AbsolutePosition + 1
End If
.Update
.MoveNext
Loop
End With
Set rst = db.OpenRecordset("Select * From tblTempSortingAndScoring Order By PercentAvail;&quot
With rst
.MoveFirst
intValue = rst!PercentAvail
intLastCount = 1
Do While Not .EOF
.Edit
If rst!PercentAvail = intValue Then
rst!SCOREPercentAvail = intLastCount
Else
rst!SCOREPercentAvail = rst.AbsolutePosition + 1
intValue = rst!PercentAvail
intLastCount = rst.AbsolutePosition + 1
End If
.Update
.MoveNext
Loop
End With
Set rst = db.OpenRecordset("Select * From tblTempSortingAndScoring Order By ACDCallsPerAvailHour;&quot
With rst
.MoveFirst
intValue = rst!ACDCallsPerAvailHour
intLastCount = 1
Do While Not .EOF
.Edit
If rst!ACDCallsPerAvailHour = intValue Then
rst!SCOREACDCallsPerAvailHour = intLastCount
Else
rst!SCOREACDCallsPerAvailHour = rst.AbsolutePosition + 1
intValue = rst!ACDCallsPerAvailHour
intLastCount = rst.AbsolutePosition + 1
End If
.Update
.MoveNext
Loop
End With
Set rst = db.OpenRecordset("Select * From tblTempSortingAndScoring;&quot
With rst
.MoveFirst
Do While Not .EOF
.Edit
rst!TOTALSCORE = rst!SCOREACDCallsPerAvailHour + _
rst!SCOREPercentAvail + rst!SCOREAvgACWTimeSec + _
rst!SCOREAvgACDTimeMin + rst!SCORERONA + rst!SCOREACDCalls
.Update
.MoveNext
Loop
End With
End Function

 

[Manowar]

Nope ... Still dings

anarchy@insync.net

e-mail me and I'll send a CSV of the data to you

 

[PaulF]

I'm at home now and all of my previous data is on my machine at work. Place a Break Point in the Code at first Set rst = statement, then when the Code Window opens up, step through the code, and see exactly where it is bombing out at.

PaulF