A table for football matches

[Dannyboy11]

Hi

I have tblTeams (pkTeamID, TeamName, Ground) and then I also have a tblMatches, which consists of pkMatchID, fkTeamID, fkTeamID2, fkRefereeID

I established a relationship between pkTeamID and fkTeamID. However, one match will consist of two teams. How can I make it so that the table will recognise which team is in fkTeamID2?

I also have a relationship between fkRefereeID and the RefereeID in my referees table.

 

[G81]

The relationship should be back to tblteams pkTeamID again, if I understand the structure you are using.

Are you having problems with a particular query?

 

[Dannyboy11]

It doesn't let me establish a relationship between both fkTeamID and pkTeamID and fkTeam2ID and pkTeamID. It just edits the original.

 

[SteveSchapel]

Dannyboy,

To achieve this, you can add the table a second time to the Relationships window.

However, I am wondering whether it is really necessary to establish Relationships in this example. The only value it could have is to cascade delete the teams from the matches, if they are deleted from the teams list - but then if a team is deleted you're going to have to re-do your whole draw anyway. Or else to prevent you entering a team in a match if that team doesn't exist in the teams table - which again won't happen in practice because of the way you create the matches. So I wouldn't myself bother with these otherwise trivial Relationships. Just my 2c.

 

[jdraw]

Hi

I have tblTeams (pkTeamID, TeamName, Ground) and then I also have a tblMatches, which consists of pkMatchID, fkTeamID, fkTeamID2, fkRefereeID

I established a relationship between pkTeamID and fkTeamID. However, one match will consist of two teams. How can I make it so that the table will recognise which team is in fkTeamID2?

I also have a relationship between fkRefereeID and the RefereeID in my referees table.

There is a data model re: football clubs at

http://www.databaseanswers.org/data_models/football_clubs/index.htm
that may help. It is based on a generic model according to the developer, but parts may be useful to you.

Good luck.

 

[Lightwave]

Here's a database that will work out matches for you and then allow you to keep scores in a league table.. Might find it useful

http://www.access-programmers.co.uk/forums/showthread.php?t=105677&highlight=sports