Random Sort Records - Ideas?! *Resolved*

[ucurl]

I'm looking for ideas.
My application requires me to create Team records made up of people from 4 different Player tables. The team record will contain 4 members. Team member 1 comes from Player Table 1, Team Member 2 comes from Player table 2, 3 from 3 and 4 from 4.
I'm using ADO recordsets.

I'd like the ability to generate the teams randomly. (so that somebody can rn this over and over and get different combinations)

What I envision is sorting the each of the 4 player tables in some sort of random order and then just reading each and building the Teams as I go.

Question.
How can I Sort the Player table(s) in a Random Order.
I expect I would create a Select Statement with the player code and some index number and then just order by the index number. I'm not sure how to do this sort?

or

Do I have to create a Temp table (4) with two fields (PlayerCode , Index) and then load the Players and generate the random index somehow? I would then just read the 4 tables and create the new team table. (agian the question is really coming up with a random index number)


Any help suggestions would be appreciated.

[Franky]

I wrote a league-system for Football. And I have an button to mix the teams by random. Here is the code:


Private Sub cmdTeamShuffle_Click()
Dim i As Integer
Dim numTeams As Integer, numRandom As Integer

txtLeagueName = Trim(Left(Me.cboLeagues.Text, InStr(1, Me.cboLeagues.Text, "->") - 1))
rsTeams.Close
sqlCommand = "SELECT * From Teams WHERE League = " & Chr(34) & txtLeagueName & Chr(34)
rsTeams.Source = sqlCommand
rsTeams.Open

numTeams = Me.lstTeams.ListCount
For i = 1 To numTeams
numRandom = Int((numTeams * Rnd) + 1)
rsTeams.Move i - 1, adBookmarkFirst
numSortOrder = rsTeams!SortNumber
rsTeams!SortNumber = numRandom
rsTeams.Update
rsTeams.Move numRandom - 1, adBookmarkFirst
rsTeams!SortNumber = numSortOrder
rsTeams.Update
Next

LeagueReread ("")

End Sub

It is not exactly what you want, but you get the idea.

nice greetings
Franky

..... If you need a algorythm for round-robbin-tournaments without any limitation on how many players are in the tournament then search the VB-Forums, i posted this algorythm here.

[ucurl]

Thanks Franky.
I've had originally posted this on the General VB board as well.
I've posted my solution back there. My solution seems pretty long winded but I got the idea and it is working for me.
Thanks for you efforts and I WILL check out your Round Robin tournament. I'm doing something similar.