Query to create round robin schedule

[Guest]

I am trying to create a simple database for creating a round robin schedule
for a tennis league. I have a table with 16 players, a table with 4 courts
and a table with each date the league meets. I need to be able to create a
random grouping of all 16 players on the various courts to ensure everyone
plays with everyone. Is there a simple query to create?

 

[Michel Walsh]

I cannot get a non-recursive/non-iterative solution, for the time slot_court
assignation.

I suggest you use a loop based approach (over a recordset), if you are using
Jet.


To find the timeslot_court assignation, starts with the full list of plays
(numbered from 1 to n(n-1)/2, n= number of players. Then, loop over each
play, and find the minimum timeslot available, given the 2 players.


Example with 6 players, 1 to 6.

p1 and p2 represent the 2 involved players.
slot is the assigned time slot for the play.

playNumber p1 p2 slot
1 1 2 1
2 1 3 2
3 1 4 3
4 1 5 4
5 1 6 5
6 2 3 x
7 2 4 y
8 2 5 z
9 2 6 a
10 3 4 b
11 3 5 ...


At step 6, in the loop, we have to find x. Players 2 and 3 are in
competition
From the previous rows, (1 to 5), we know that player 2 is already involved
in slot 1
we know that player
3 is already involved in slot 2
so x=3, the minimum slot available

At step 7, we know player 2, from previous rows, is involved in slots 1 and
3
player 4 is involved in slot 3
so y=2, the minimum slot available

At step 8, we know slots 1, 2, and 3 are not available for player 2
we know slots 4 is not available for player 5
so z=5

At step 9, slots 1, 2, 3, and 5 are not available for player 2
slot 5 is not available for player 6
so a=4



and so on. Also, when a time slot is used 4 times, it cannot be used
anymore (you only have 4 courts).



That procedure is iterative, but you can built it ONE time, save it in a
table, and if the number of players is constant, 16, you just have to
replace the number by the real player names.




Hoping it may help,
Vanderghast, Access MVP

 

[Gary Walter]

"J Dizzle Fizzle"wrote:

I am trying to create a simple database for creating a round robin schedule
for a tennis league. I have a table with 16 players, a table with 4
courts
and a table with each date the league meets. I need to be able to create
a
random grouping of all 16 players on the various courts to ensure everyone
plays with everyone. Is there a simple query to create?

Hi JDF,

I always liked Joe Fallon's simple "algorithm"
when it comes to "round robin."

<quote>

The usual way of doing round robin competitions, say in a
chess or bridge tournament, is simply to line up the contestants facing
each other. Say there are 6 teams:

1 2 3
6 5 4

On the next round, one team stays fixed (say 6* in this example), and the
others rotate:

2 3 4
6* 1 5

On the next round it would be:

3 4 5
6* 2 1

and so on, for the 5 steps it takes for everyone to get back to where they
started. The contestants can rotate either clockwise, or
counterclockwise, it doesn't matter. And any one of them can stay fixed,
it doesn't matter. If there are an odd number of teams, then just add a
"dummy" team to get to an even number, and whoever faces the dummy sits
out that round; usually the dummy is the fixed-position team, and the
board is not set up for that position.

I think this is why it is called a "round robin", because the teams cycle
around, facing all of the possible opponents.

 

[Michel Walsh]

The algorithm can then be simplified, since it is a simple permutation.


With:

0* 1 2 3 4 5 6 7
F E D C B A 9 8

(F=15, E=14, ... I used single letter for proper spacing)


becoming:


0* F 1 2 3 4 5 6
E D C B A 9 8 7

can be driven by a table:

Perms 'table name
f t ' fields name
0 0
1 15
2 1
3 2
4 3
5 4
6 5
7 6
8 7
9 8
10 9
11 10
12 11
13 12
14 13
15 14 ' data


We will also need a companion table that gives the 'column' number

Coords ' table name
who col ' fields
0 1
15 1
1 2
14 2
2 3
13 3
3 4
12 4
4 5
11 5
5 6
10 6
6 7
9 7
7 8
8 8



Two 'who'-s having the same 'col' value are playing together.


So the first two time slots (since you have 4 courts, the first 'round' of
the round-robin need 2 time slots to play the 8 games) identify the players
through the table Coords.

The next round is given by:

SELECT p.t, c.col
FROM Perms AS p INNER JOIN Coords AS c
ON p.f=c.who
ORDER BY c.col, p.t

(the order by is just used to 'visually' bring close together the players of
the same 'court' )


t col
0 1
14 1
13 2
15 2
1 3
12 3
2 4
11 4
3 5
10 5
4 6
9 6
5 7
8 7
6 8
7 8




The third round, by:


SELECT p.t, c.col
FROM (Perms AS p INNER JOIN Perms AS p1
ON p.f=p1.t ) INNER JOIN Coords AS c
ON p1.f=c.who
ORDER BY c.col, p.t



t col
0 1
13 1
12 2
14 2
11 3
15 3
1 4
10 4
2 5
9 5
3 6
8 6
4 7
7 7
5 8
6 8





.... and so on, adding inner join successively (or interatively on the
previous result).


Again, in each result, col = 1 to 4 indicate a court, for a time slot, and
col= 5 to 8 indicate 4+ court number, for the next time slot.


The procedure is still iterative, but does not required a recordset.



Vanderghast, Access MVP