Fixture Generating

[dazza041]

Hey, I need some help on developing a fixture list for a school games competition. The system has 36 participants from 6 different classes and within the classes there are students in which are belonging to different houses (Yellow and Green). Therefore 18 participants from green house and 18 from yellow.

The rules of the competition are as follows:

Any player has to play four games
any player cannot play the same player twice
Any player cannot play the same game twice
Any player cannot play the same player from the same house colour or opposing house colour within the same class.

Any ideas in which to go about this?

Many thanks
daz

 

[Jon K]

The rules of the competition are as follows:

Any player has to play four games
any player cannot play the same player twice
Any player cannot play the same game twice
Any player cannot play the same player from the same house colour or opposing house colour within the same class.

Any ideas in which to go about this?

I believe it will be difficult to prepare a fixture list that can completely satisfy the four rules.


Resolve the two Player rules in a query
I would use a query to produce a fixture list of all the possible player combinations that remain after taking into effect the two Player rules:-

Any player cannot play the same player twice
Any player cannot play the same player (a player?) from the same house colour or opposing house colour within the same class.

(You can add the player list table twice in a query, one for PlayerA and the other for PlayerB and set their join condition in the Where Clause.)

Resolve the two Game rules in VBA
Then in VBA, I would append the fixture list from the query to a temporary table, open the temporary table as a recordset, and loop through the records in the recordset to allot (not sure if it is the correct word to use!) the games to the players.


However, since allotting a game to one player simultaneously allots it to another, the choices of records available for a player are greatly reduced by the combined effect of maximum four games and the "cannot play the same game twice" rule. So at the end of the loop, most likely a few players will end up being allotted fewer than four games, thus defying the "has to play four games" rule.

As a loop will go from the first record down to the last, opening the recordset with a different Order By clause may slightly change the allotment results. With a bit of luck, you may eventually find an Order By clause that can successfully allot four games to each player.
.

 

[dazza041]

Still having some problems with the whole creating fixtures for just the players :S any help?

 

[modest]

There are better tools out there to solve such problems. Not to mention, this is an algorithm question, not a VBA question.

 

[dazza041]

Tools such as. I mean the database is basically the back end of the application.

What tools out there would be better?

 

[Jon K]

I have found that if the data from the two houses are symmetric, that is each house with 3 players from each of 6 classes, and if the players are entered in the player table like the following, you can allot four games to each player in Access in the way I described in my previous post.

[b]ID	House	Class[/b]
1	Green	Class 1
2	Green	Class 1
3	Green	Class 1
4	Green	Class 2
5	Green	Class 2
6	Green	Class 2
7	Green	Class 3
.
.
.
19	Yellow	Class 1
20	Yellow	Class 1
21	Yellow	Class 1
22	Yellow	Class 2
23	Yellow	Class 2
24	Yellow	Class 2
25	Yellow	Class 3
.
.
36	Yellow	Class 6

The above layout enables the query of "possible player combinations" to put the Green house and Yellow house players in two separate columns for the VBA code to Order By the Yellow house player IDs.


I have attached my test database. You can open the "Update tblTemp" form and click on the command button to update the temporary table with the games. The VBA code used is in the On Click event of the command button.

After the update, the subform should show that no players have been allotted fewer than 4 games. And the query "qry3FinalFixtureList" should return a fixture list of 144 games.


I have also found that if I change the player list data a bit, some players will be allotted fewer than 4 games. So the success in this case is probably due to the symmetry of the data.
.

 

[modest]

QMWIN is statistical software that is good for Transportation, Transhipment, and Assignment problems. In your case, it would be an assignment. You simply add your four constraints and bam it prints out all the possible solutions.

 

[modest]

I've started looking at doing this programmatically. I'm still stuck on the:

2)Any player cannot play the same player twice
4)Any player cannot play the same player from the same house colour or opposing house colour within the same class.

If they could play the same player from the same house color or opposing house color w/in the same class.. wouldn't they still be breaking rule 2 (the same player)
Or are you trying to say that they just can't play someone that's in the same class as someone they've already played?

 

[EMP]

Originally posted by Jon K

Resolve the two Player rules in a query

Any player cannot play the same player twice
Any player cannot play the same player (a player?) from the same house colour or opposing house colour within the same class.

I think there's a typo in the second player rule as Jon remarked.

^

 

[dazza041]

The rules of the competition are as follows:

Any player has to play four games
any player cannot play the same player twice
Any player cannot play the same game twice
Any player cannot play the same player from the same house colour or opposing house colour within the same class.

yer sorry about the typo what it was meant to be was that players cant play each other twice. and that players cant play anyone from their class or in their house colour. Sorry.

Hopefully will be able to sort this out, cheers for the advice fellas im gonna give this ago and then come back with any possible errors that may concur.

Many thanks
daz

 

[dazza041]

hey im having problems (whilst using the test run) in displaying the players names and game names instead of the ID's.

Would i link this through to tblgames (with SNAP, CRIBBAGE, SPILIKINS, and SCRABBLE in)?

and would the same apply to the players nameS?

 

[Jon K]

I have revised the code to include the game names. You can fill in the student names and class names in the player table and then run the code once from the form.

Then you can use either query 2 or query 3 as the fixture list.
.

 

[dazza041]

Right, attached is my database.

Firstly i have the students table filled in apart from the name (which will be filled in whilst the program runs).

Then i have a games table which will also be entered whilst the the program is running. Then the tricky parts are valid; I have a possiblematches table and displays the fuxtures. In the first update query button fucntion on the form it is clear how that works and what the SQL code does whislt doing it. However, due to this being a backend database and my program being written in Delphi; is there a way in calling this form button click (updatetbl) to run the code, to generate fixtures?

Thanks
daryl