Random Sort

[Javelin]

Hi,

I have/run a football (soccer) league database and there are at least 64 teams in it - but this could rise up to 90 teams.

Currently I enter the Team names and their specific details - then I create individual groups/leagues - Once I have done both of these I then assign/add each team to a specific group/league - up to a maximum of 8 teams per league.

However - I want the teams to be randomly sorted into the groups/leagues (maximum of 8) - this action will be completed at the touch of a button.

Have I explained myself properly?

Can someone please help?

Jav

 

[Tiro Cupidus]

Here's something I came up with. Someone say if there's a better way please, I'd love to know. =)

Function fSetLeagueNumbers()
   Dim numTeamsMod, numLeagues, iTeam, iTeamMod, rsTeams, sql
   Dim i As Integer, iTemp As Integer, strTemp1 As String, strTemp2 As String
   Set rsTeams = CurrentDb.OpenRecordset("tbl1")
   Const numTeams = 64
   Const maxTeamsPerLeague = 8
   numTeamsMod = numTeams Mod maxTeamsPerLeague
   numLeagues = numTeams \ maxTeamsPerLeague + (IIf(numTeamsMod = 0, 0, 1))
   Dim myArray(1 To numTeams, 1 To 2)
   For iTeam = 1 To numTeams
      iTeamMod = (iTeam - 1) Mod maxTeamsPerLeague + 1
      myArray(iTeam, 1) = iTeam
      myArray(iTeam, 2) = iTeamMod
   Next iTeam
   Randomize
   DoCmd.SetWarnings False
   i = 1
   Do Until rsTeams.EOF
      iTemp = Int((numTeams - i + 1) * Rnd + i)
      sql = "UPDATE tbl1 SET League = " & myArray(iTemp, 2)
      sql = sql & " WHERE TeamName = '" & rsTeams![TeamName] & "';"
      DoCmd.RunSQL sql
      strTemp1 = myArray(iTemp, 1)
      strTemp2 = myArray(iTemp, 2)
      myArray(iTemp, 1) = myArray(i, 1)
      myArray(iTemp, 2) = myArray(i, 2)
      myArray(i, 1) = strTemp1
      myArray(i, 1) = strTemp2
      rsTeams.MoveNext
      i = i + 1
   Loop
   DoCmd.SetWarnings True
End Function

TeamName and League are names of fields in tbl1.

 

[Javelin]

Thanks Tiro - your a star!

 

[Tiro Cupidus]

No problem. =) Glad it helped you out.