Sorting records

[BravoEcho]

Who can help me ...

I created a querry called " SCORES" with the following structure:

POINTS | NAME | OPTION1 | OPTION2 | OPTION3

8 | John | 2 | 1 | 3

9 | Robert | 1 | 2 | 3

10 | Willy | 3 | 1 | 2

7 | Wu | 1 | 2 | 3
and so on

or otherwise

POINTS | NAME | 1 | 2 | 3

8 | John | OPTION2 | OPTION1 | OPTION3

9 |Robert | OPTION1 | OPTION2 | OPTION3

10 |Willy | OPTION2 | OPTION3 | OPTION1

7 |Wu | OPTION1 | OPTION2 | OPTION3
and so on

where OPTION1, OPTION2 and OPTION3 with values 1, 2, 3 are the preferences of the candidates for the options with interest value of 1, 2 or 3.

OPT1 has a maximum of X winners
OPT2 has a maximum of Y winners
OPT3 has a maximum of Z winners


I want to order the candidate in the following way:

Each candidate must be tested separately according to the score obtained and expressed choices, if it can occupy a place in the first option is taken, if in that option there are no longer places it must be tested if he can occupy a place on the second option, if there are places on this option he will be assigned to this option, and if there aren't any places it must be tested if he can occupy a place in the third.
Just proceed with the next candidate and repeat the operations from the first option.

Thank you anticipated for your help!

http://www.dbforums.com/editpost.php?do=editpost&p=6406744

 

[Kafrin]

I would suggest using another field, something like OptionWin, to record which option the candidate wins in. Then you can write a sub to run through all the candidates in whatever order you specify and assign them to the options by putting 1,2 or 3 in the OptionWin field. If you want to show that a candidate has been tested but has not won in any option, put a 0 in the field (make sure the field defaults to null, so the null ones show as not having been tested).

So you'll loop through your recordset of candidates and assign up to X of option 1. Then loop through those with IsNull(OptionWin) and assign up to Y for option 2. Then again to assign up to Z for option 3. If you are going to keep track of tested but unassigned candidates, loop again assigning all remaining candidates to OptionWin=0.