Question Fooball System

[fsagwe]

Dear all,
I have tried to work on these attached football system database.I got problems in:
1.Generating automatic fixtures(home and away)for a league of 20 teams.(query)
2.Automatically assigning referees both home and away(query)
3.Functions to come up with a league table(query)
4.Report generation in ascending order.

-I kindly request help from these dear Access community

Kind Regards,

 

[essaytee]

I believe your schema needs a little work.

At the heart of this type of system, the recording of scores can be handled by two records (two rows of a table). After that, calcuations can be done to figure out all the rest.

To get you started I'd suggest three tables, as follows:

tblGame (Game_ID, date, description ......)
tblTeam (Team_ID, name .....)
tblTeamGame (Team_ID, Game_ID, Score)

Some data to illustrate:

Man Utd (5) v Liverpool (1) (Man Utd win, of course)
Arsenal (3) v Man City (4)

Game 35 is Man Utd v Liverpool
Game 40 is Arsenal v Man City

The entries in the TeamGame table would be:

Team ID      Game ID       Score
Man Utd       35              5
Liverpool     35              1
Arsenal       40              3
Man City      40              4

As you can see, searching Game 35 will retrieve two records and from there the FOR and AGAINST can easily be determined.

Hope this helps.

Steve.

 

[fsagwe]

Thank you very much on the guide.Should I follow the same method in assigning For and Against Referees.

 

[essaytee]

Thank you very much on the guide.Should I follow the same method in assigning For and Against Referees.

I'm not understanding your concept of FOR and AGAINST referees.

There is only one referee per game (not accounting for assistants). Therefore, simply add a FK of Referee_ID to the Game table.

 

[fsagwe]

[FONT=&quot]Thanks,
well on TeamGame table ,I can use GameID to extract two records .But my problem still is on fixtures:20 teams taking part in a league,each team must play (Home & away) and that the coach of each team will act as a referee in the pool.But,the coach/referee can not officiate his /her team during the matches and the entire league.

Regards,
[/FONT]

 

[essaytee]

[FONT=&quot]Thanks,
well on TeamGame table ,I can use GameID to extract two records .But my problem still is on fixtures:20 teams taking part in a league,each team must play (Home & away) and that the coach of each team will act as a referee in the pool.But,the coach/referee can not officiate his /her team during the matches and the entire league.

Regards,
[/FONT]

I have never developed a 'league' type database before but I am familiar with the home/away concept.

If you think about it, the TeamGame table can also be considered a Fixture table. At the time of fixturing, the Fixture table (TeamGame table) is populated with details that are known (Team v Team, Date, Referee (maybe), Location). The only information not known will be the scores (referee may have to be updated). Enter the scores at the conclusion of the game.

There are also additional fields that could be added to the TeamGame (or Fixture) table, those being related to Yellow & Red Cards (I believe you referenced them in your attached db). Again, these ultimately can be calculated to however you wish to use them.

I don't understand how a coach must referee a match to which he is not associated. If said coach is refereeing another match who is coaching his team? In any regards, you are now delving into business rules of your system as to who can and can not referee matches. I'd suggest get the basics down first.

At this point I think it maybe wise to create the three tables above, along with tables for Teams and Referees, enter some sample data (directly into the tables - I'm not suggesting full blown forms etc) and then test various queries against the data. I can assure you, if you do this, you will learn heaps and come to grips with the schema. It will also highlight any deficiencies and that's what testing is all about. At this point your business rule regarding coaches and refereeing can be worried about later as that doesn't interfere with this testing process.