Soccer Fixtures

[ShaunWillmott]

I'm trying to create a Db to create fixtures for my local Soccer sevens Association. I have created a table with a list of teams (usually between 18-22 teams) and a table with:

WeekNo, Date, HomeTeam, AwayTeam

My problem is not coding or Db creation but the method fro creating Fixtures I don't know how????

Anybody help, this must have been done before!!!!

Cheers

Shaun

 

[CJBIRKIN]

Hello

Try the DB below(ACCESS 2000). It is just an start.

Chris

 

[ShaunWillmott]

Chris,

Many thanks for the Db, I'm not quite sure how it works? do I have to enter all the fixtures in manually?

Here is where I have got to so far, my issue is that there are 18-20 teams per league and 4 leagues (some 80 teams), the teams normally only play each other once as there is not enough time (season runs from End of Aug to start of Dec (15-16 weeks)

But code needs to try and have each team play Home/Away as fairly as possible. So far I have the code creating fixtures for playing twice (once home & once away), but it doesn't get the weeks or dates right (i.e. the fixtures are correct but all mixed up.

Cheers

Shaun

 

[CJBIRKIN]

Hello

Please forgive my ignorance of your predicament. I've run your code and would appear to work fine. I've tried doing a sort on the week in your table it looks happier then. See your fixtures table. If this isn't what you expected please provide more detail.

Chris

 

[ShaunWillmott]

If you select the WeekNo or Date field and sort it the output looks like this:

WeekNo Fixture Date HomeTeam AwayTeam
1 31-Aug-02 Team D Team A
1 31-Aug-02 Team C Team A
1 31-Aug-02 Team B Team A
1 31-Aug-02 Team A Team B
2 07-Sep-02 Team D Team B
2 07-Sep-02 Team C Team B
2 07-Sep-02 Team B Team C
2 07-Sep-02 Team A Team C
3 14-Sep-02 Team D Team C
3 14-Sep-02 Team C Team D
3 14-Sep-02 Team B Team D
3 14-Sep-02 Team A Team D

As you can see Team A plays 4 games on the same day????, but if you go through the fixtures (ignore week & date) the combination is correct.

The code should only create fixtures so that the teams play once (making 1/2 the number of games), therefore Week 1 should be:

Team A v Team B
Team C v Team D

Week 2 should be:

Team B v Team C
Team D v Team A

and finally Week 3 should be:

Team C v Team A
Team B v Team D

It's easy with 4 teams but very time consuming with 20 teams (let alone 80???)
How do I get code to create this automatically??????? there must be a pattern or formula for doing this somewhere???

Thanks for your help so far.

Cheers

Shaun

 

[sambo]

Blame this on American ignorance, but what the heck is a fixture?

 

[ShaunWillmott]

Sorry

A fixture in the UK is the same as a Schedule/Roster in the States i.e. a List of the matches to be played between a group of teams over a period of time.

Regards

Shaun

 

[CJBIRKIN]

Ok call me Mr Thicky Pants.

I can see exactly what you mean, as for a solution. Well...

i'll have a think this evening if you come up with something good luck!

Chris

 

[smed]

Shaun

I appologise if it is a bit messy but the attach db works.

Within the module, specify the number of teams and execute the "CalculateFixture" function.

Smed

 

[ShaunWillmott]

Smed,

Brilliant.

A very clever and very simple solution.

Once again this board has triumphed.

Cheers

Shaun

 

[ShaunWillmott]

Fully Working Version

Chris/Smed,

Here is a working version, I still need to create the forms, and add some additional rules (breaks during the season for Holidays, Split league between Bruce and Wallace, etc.) but the hard part is done (Thanks again Smed).

This will save so much time at the start of the Season (we normally have to force people to be the Match Sec. and then wait ages for them to do the fixtures, mostly changes have to be made just before the season starts and it's chaos as the Match Sec tries to redo the Fixtures.

This solves all of these problems.

Thanks for all of your help.

Best regards

Shaun

 

[DALIEN51]

If Your Interested......

I think the easiest method for 20 teams is.....

20 teams will create 19 rounds of matches or 38 if they play each other twice. Split the teams into 2 groups of ten teams each. For the first 10 rounds of matches group1 teams play group 2 teams and thus you can alternate these home and away so after the first 10 rounds of games everyone will have played 5 home matches and 5 away matches...

Then sub divide group1 into 2 groups of 5 teams...group A and group B
also sub divide group2 into 2 groups of 5 teams group C and group D
for the next 4 rounds of matches group A teams play group B teams and group C teams play group D teams again you can alternate home and away exactly so after 14 rounds of games each team will be axactly on 7 home games and 7 away games. The reason you only play 4 rounds and not 5 is that now group A teams will have to play each other (likewise group B, C & D) and thus with an odd number of teams the "spare" team within a group will have to have a match left against the opposite group (A-B & C-D) so they dont have a vacant match in that round!...hope your still with me...

So arrange each of the round of matches for the 5 teams in each group so that the "spare" team in each set of groups (A-B & C-D) are the ones yet to play each other.

When completed for all 19 rounds hey presto! If your playing twice simply copy the table to itself reversing the home and away teams and incrementing the round number of date as appropraite...

Hope this helps..

NEIL ADCOCK

 

[Mile-O]

Why are you answering a post that 'died' two years ago? Especially one that's concluded/resolved!

And, on another soccer thread, that 'died' midway through last year?

 

[DALIEN51]

Sorry!

Just thought it might help anyone who was trawling through!

Did ya understand how I explained it?

Neil

 

[DALIEN51]

Is It Possible?

Shaun

I appologise if it is a bit messy but the attach db works.

Within the module, specify the number of teams and execute the "CalculateFixture" function.

Smed

IS IT POSSIBLE TO ADD A PIECE OF CODE SO THAT AT NO POINT IN THE SEQUENCE IS THE DIFFERENCE BETWEEN A TEAMS NUMBER OF HOME GAMES AND NUMBER OF AWAY GAMES EXCEEDING 1?

Neil

 

[Jon K]

Soccer Fixture Lists can also be created in queries using a self-join Cartesian recordset.

The attached database contains two queries:-
A fixture list for 20 teams playing each other twice (that is a permutation of 20 taken 2 at a time).
A fixture list for 20 teams playing each other once (that is a combination of 20 taken 2 at a time).

The autonumber field in the table is needed for the latter query - the combination.