Soting and scoring

[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

 

[kathryn]

OK, the way I would approach this is to create a number of queries: qryCallsScore, qrySpeedScore, qryTimeScore.
Each query should only have the employees' IDs in it in order from Worst to Best.

In your example:

qryCallScore would return

Mary's Id
Jane's ID
Joe's ID

qrySpeedScore would return
Joe's ID
Jane's ID
Mary's ID

etc...

Create a table, call it tblResult, with the following two fields:
ID
Score



Then use code to open each query.

*****Begin Code*****

dim rstCall as recordset
dim rstSpeed as recordset
dim rstTime as recordset
dim rstResult as recordset
dim x as integer 'for score

set rstCall = Currentdb.openRecordset("qryCallScore&quot
set rstSpeed = Currentdb.openRecordset("qrySpeedScore&quot
set rstTime = Currentdb.openRecordset("qryTimeScore&quot
set rstResult = Currentdb.openRecordset("tblResult&quot

rstCall.movefirst
rstSpeed.moveFirst
rstTime.movefirst

x = 1 'the initial score

Do until rstCall.eof 'I chose Call at random, you could test any of the recordsets

rstresult.addnew
rstresult!id = rstcall.ID
rstresult.score = x 'This will give a score of 1 to worst
rstresult.update

rstresult.addnew
rstresult!id = rstTime.ID
rstresult.score = x
rstresult.update

rstresult.addnew
rstresult!id = rstTime.ID
rstresult.score = x
rstresult.update

x = x+1 'increment score

rstcall.movenext
rsttime.movenext
rstspeed.movenext

loop

*****End Code******

Then you just need to run a Group Totals query on tblResult and group by ID and sum the score.

I think that there is a way to simplify this, by having your three queries include an incremented score, but I cannot for the life of me think of how to do it.

Any way, the above should work, although, it will probably need some modifications, since i am doing it OTTOMH.

Post for more info if you need it.

Kathryn