Football League Table from Fixtures Table (1 Viewer)

[Cassy01]

Hi i am wandering if anyone can help me complete this problem:

i have a table full of fixtures/results in the following form:

tblFixtures
fixtureID*
compID
fixDate
fixTime
HomeTeamID
AwayTeamID
HomeScore
AwayScore
PitchID
RefereeID

is it possible to create a league table on those results.

1. There is more than one competition in the table so it would have to be by compID
2. There is fixtures without results so they would have to be excluded as well.

Any help?

 

[Sum Guy]

You have set up a very nasty one here.

You would have to create a query to sift the results you have entered into the table into some useable shape.

From the look of the data you have in the table, this is going to be a very complicated and involved process. It's not impossible, but its going to be difficult. For example, you have to connect the hometeamID to the Homescore and the same with the Away team. Does each team have the same ID if they are home or away. If not, you will then have to find a way to connect the Home and AwayID to the same team.

Then, you will have to compare the results of the matches ie.Team A was away at Team B, who scored the higher number of goals and therefore won the match.

Good luck.

 

[anthonys]

Solution?

Hi,

i have attached a potential solution to your problem.

The mdb contains your fixtures table, a guess at your team table and 2 queries.

The query called results is a union query that calculates the number of points each team earns in a fixture. the second query called leaguetable is based on the results query and shows your league table and from this you could create reports.

Hope this is what you are after

Ant