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.