Random Sort Records - Ideas?! *Resolved*

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.

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.

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.