Fixtures

[enc]

I don't know if this is a bit far fetched or even possible within Access but for this reason I post here in hope somebody will understand my predicament

I've basically got a sports league database. One of my tables contains a complete list of every team in the league and this is the tables only column. As each team has a unique name there is no need for any other column other than the teams name itself. Imagine:

Team
------
Team1
Team2
Team3 etc etc.

What I want to do is have my league fixtures automatically generated so that each team will play each other once per season. So, when a user has entered all the teams into my database then they will just have to press a button on one of my forms which will then assign all possible fixtures into a new table with the format being:

AutoNumber | Team | Team2
-----------------------------
1 | Team1 | Team2
2 | Team1 | Team3
3 | Team2 | Team3

Does anybody have any ideas to how I can implement this?

 

[Kraj]

As each team has a unique name there is no need for any other column other than the teams name itself.

Ack! Boo! Hisssss!

Always always always have a field that does nothing but function as a primary key and make it autonumber. To the best of my knowledge there is absolutely no scenerio in which you would not want to do that.

As far as creating the table, play around with crosstab queries. That may be the ticket.

 

[enc]

Its to avoid numbers showing instead of names in my lookups for my forms etc.

I've just realised I never searched for this topic and there seems to be a fair bit on it so I'll give some of the previous suggestions a whirl.

 

[Kraj]

Its to avoid numbers showing instead of names in my lookups for my forms etc.

It shouldn't matter. If you use a combo box it will show the name and hide the key.

If you are using the databsae for nothing but generating the table then you'll probably be OK, but if you store any information in the database at any time that references the team name, then you will get hosed sooner or later.

 

[Jon K]

I posted a sample database in this thread:-
http://www.access-programmers.co.uk/forums/showthread.php?t=34839

You can change the query "qryFixture List - playing once" into an append query to insert the query results into a table with three fields:
AutoNumber | Team | Team2

 

[enc]

Yes. I found that earlier and that is what I'm now using. Thanks