Recordsets

[John Kirkman]

Hello All,

I have a database of members ( 90 to 100) and I am trying to randoming
assign them to 10 teams. The following Sub will assign each member to a team
from 1 to 10, BUT the results will not be evenly distributed. For example
team 1 may have 14 members which team 2 may have 9 members.

What I would like to do is if there are 92 members then have 2 teams of 10
members and 8 teams of 9 members. Not 9 teams of 10 and 1 team of 2.

I am not sure I have posted this to the correct newsgroup, so if I am in the
wrong place, my apologies.

Thanks for any help.


*** Abreacted Sub
Dim rst As ADODB.Recordset
With rst
Do While Not .EOF
' GET RANDOM NUMBER BETWEEN 1 AND !10
intTeam = Int((intNoTeams * Rnd) + 1)
.Fields("Team") = intTeam
.MoveNext
Loop
.UpdateBatch
End With

 

[Michel Walsh]

Hi,


I would rank the random number (generated from 0 to 1): who is the
smallest, who is the second smallest, .... up to 92 and then, take these
ranks to make the teams : 1 to 10, 11 to 20, 21 to 29, 30 to 37, ....

In a query, q1:

SELECT *, Rnd(Team) As RN FROM Teams


Another query

SELECT *, DCount("*", "q1", "RN<=" & RN ) As Rank FROM q1


supply you with the "rank" number. It is up to you to then find the
splitting points for the teams.



Hoping it may help,
Vanderghast, Access MVP

 

[John Kirkman]

Dear Michel,

Many thanks. This gets me going in the right direction.

John