P
Peter Hibbs
Hi All
I have a table called tblTeams. It has (amongst others) a unique text
field called TeamID. There is one record for each team, the number of
teams will vary from season to season (typically between 8 and 16) and
there are always an even number of teams.
What I need to do is create a fixture table for the season where each
team plays every other team. It works like this - each team plays
another team each week so if, for example, there are 14 teams there
would be 7 matches each week. The number of weeks required for each
season, therefore, will be the number of teams minus 1 (as a team
cannot play itself).
For example, suppose there are 6 teams in the league called TeamA to
TeamF. The fixture list may look something like this :-
Home Away Home Away Home Away
Week 1. TeamA v TeamB, TeamC v TeamD, TeamE v TeamF.
Week 2. TeamA v TeamC, TeamB v TeamE, TeamD v TeamF.
Week 3. TeamA v TeamD, TeamB v TeamF, TeamC v TeamE.
Week 4. TeamA v TeamE, TeamB v TeamD, TeamC v TeamF.
Week 5. TeamA v TeamF, TeamB v TeamC, TeamD v TeamE.
The aim is to write some VBA code which will enter the team IDs and
week Nos into a fixtures table called (tblFixtures) like this :-
(HomeTeam) (AwayTeam) (WeekNo)
TeamA TeamB 1
TeamC TeamD 1
TeamE TeamF 1
TeamA TeamC 2
TeamB TeamE 2
TeamD TeamF 2
TeamA TeamD 3
TeamB TeamF 3
TeamC TeamE 3
TeamA TeamE 4
etc, etc ....
I have created a 'Cartesian Product' query (with some help from Baz)
which has two copies of tblTeams (with no joins) and a criteria which
produces a list of fixtures, as follows :-
SELECT tblTeams_1.TeamID AS HomeTeam, tblTeams.TeamID AS AwayTeam, ""
AS WeekNo
FROM tblTeams, tblTeams AS tblTeams_1
WHERE (((tblTeams_1.TeamID)>[tblTeams].[TeamID]));
This produces a list where every team plays every other team once and
it also includes a blank field called WeekNo for each fixture. My idea
is to create an array, recordset or temporary table (whichever is
easiest) of the fixtures, iterate through the list filling in the
WeekNo field with a suitable week number (as in list above) and then
insert the results into the Fixtures table (along with some extra info
such as fixture date, ID number, etc).
At first glance this looks simple, just scan the list with a For-Next
loop and fill in the WeekNo fields from an incrementing variable, or
whatever. Not so!
The problem is that it is easy to allocate week numbers for the first
few weeks but then find that there is no solution for the remaining
weeks. For example (using the teams above) if you start off like this
--
In week 1 let TeamA play TeamB, TeamC play TeamE and TeamD play TeamF.
In week 2 let TeamA play TeamC, TeamD play TeamE and TeamB play TeamF.
In week 3 let TeamA play TeamD, TeamC play TeamF and TeamB play TeamE.
Now weeks 4 and 5 will fail because all combinations have already been
used.
I need to find some (simple) algorithm which can scan the list and
allocate valid week numbers for each fixture but have so far failed.
Can anybody come up with some code (or pseudo code) which could do
this.
Thanks.
Peter Hibbs.
I have a table called tblTeams. It has (amongst others) a unique text
field called TeamID. There is one record for each team, the number of
teams will vary from season to season (typically between 8 and 16) and
there are always an even number of teams.
What I need to do is create a fixture table for the season where each
team plays every other team. It works like this - each team plays
another team each week so if, for example, there are 14 teams there
would be 7 matches each week. The number of weeks required for each
season, therefore, will be the number of teams minus 1 (as a team
cannot play itself).
For example, suppose there are 6 teams in the league called TeamA to
TeamF. The fixture list may look something like this :-
Home Away Home Away Home Away
Week 1. TeamA v TeamB, TeamC v TeamD, TeamE v TeamF.
Week 2. TeamA v TeamC, TeamB v TeamE, TeamD v TeamF.
Week 3. TeamA v TeamD, TeamB v TeamF, TeamC v TeamE.
Week 4. TeamA v TeamE, TeamB v TeamD, TeamC v TeamF.
Week 5. TeamA v TeamF, TeamB v TeamC, TeamD v TeamE.
The aim is to write some VBA code which will enter the team IDs and
week Nos into a fixtures table called (tblFixtures) like this :-
(HomeTeam) (AwayTeam) (WeekNo)
TeamA TeamB 1
TeamC TeamD 1
TeamE TeamF 1
TeamA TeamC 2
TeamB TeamE 2
TeamD TeamF 2
TeamA TeamD 3
TeamB TeamF 3
TeamC TeamE 3
TeamA TeamE 4
etc, etc ....
I have created a 'Cartesian Product' query (with some help from Baz)
which has two copies of tblTeams (with no joins) and a criteria which
produces a list of fixtures, as follows :-
SELECT tblTeams_1.TeamID AS HomeTeam, tblTeams.TeamID AS AwayTeam, ""
AS WeekNo
FROM tblTeams, tblTeams AS tblTeams_1
WHERE (((tblTeams_1.TeamID)>[tblTeams].[TeamID]));
This produces a list where every team plays every other team once and
it also includes a blank field called WeekNo for each fixture. My idea
is to create an array, recordset or temporary table (whichever is
easiest) of the fixtures, iterate through the list filling in the
WeekNo field with a suitable week number (as in list above) and then
insert the results into the Fixtures table (along with some extra info
such as fixture date, ID number, etc).
At first glance this looks simple, just scan the list with a For-Next
loop and fill in the WeekNo fields from an incrementing variable, or
whatever. Not so!
The problem is that it is easy to allocate week numbers for the first
few weeks but then find that there is no solution for the remaining
weeks. For example (using the teams above) if you start off like this
--
In week 1 let TeamA play TeamB, TeamC play TeamE and TeamD play TeamF.
In week 2 let TeamA play TeamC, TeamD play TeamE and TeamB play TeamF.
In week 3 let TeamA play TeamD, TeamC play TeamF and TeamB play TeamE.
Now weeks 4 and 5 will fail because all combinations have already been
used.
I need to find some (simple) algorithm which can scan the list and
allocate valid week numbers for each fixture but have so far failed.
Can anybody come up with some code (or pseudo code) which could do
this.
Thanks.
Peter Hibbs.