VBA Logic Problem

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.
 
D

Douglas J. Steele

Lots of luck! <g>

Seriously, it's a very non-trivial problem: one that I've toyed with a few
times over the years, and never come up with a solution that's satisfied me.

The simplest approach I've found is to store sample schedules for each of
the combinations between 8 & 16 teams and use those as a template.

If you do come up with a working algorithm, I'd love to see it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Peter Hibbs said:
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.
 
P

Peter Hibbs

Hi Douglas

I am using the 'template' method for this seasons 14 teams at the
moment. I was hoping not to have to create several more tables for the
other combinations. I will have to give it some more thought, maybe I
can come up with something.

Peter Hibbs.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Unusual Query Problem 3
Schedule Rotation 3
Ranking order of cells. 9
Evolution of scatter dots 1
Making a sports league schedule 2
Trouble with a Formula I need help with??? 7
Database Design 7
League Fixtures 8

Top