Hey im a new comer to database design and getting trouble attempting to figure that one out. I've two tables Team and Fittings. Team has rows of football teams and Fixture has 2 of individuals football teams in every row (home and away team). I wish to link team id by_team and away_team however it does not let me. Please let me know the way i can solve this.

Here's a picture of my tables/associations http://i49.tinypic.com/288qwpg.jpg

Here's what you might do:

Team Table

Team_ID
Team_Name

Fixture Table

Fixture_ID
Home_Team_ID
Away_Team_ID

The Home_Team_ID and Away_Team_ID links towards the Team table.


Here's the SQL to tie them together (completed in OpenOffice.org Base, however i hope it matches your needs too):

SELECT Fixture_ID,
       Home_Team.Team_Name AS Home_Team_Name,
       Away_Team.Team_Name AS Away_Team_Name
  FROM Team Home_Team,
       Team Away_Team,
       Fixture
  WHERE Home_Team.Team_ID=Fixture.Home_Team_ID
    AND Away_Team.Team_ID=Fixture.Away_Team_ID

As you can see, you will find two references towards the team table, but they are separated with aliases (Home_Team and Away_Team). I really hope this can help.

Because you have to reference two different teams (home and away) in the fixture table, you have to add they table two times. Should you only add it once, then both home and away must connect with exactly the same team, which obviously will not make any sense.

They in one table is became a member of towards the home team id. And also the team in the second team table is became a member of towards the away team id.

In access, you may also relabel the table - provide an alias. (Click the table an hit F4. This raises the qualities - you are able to key in an alias.) For that table associated with home team, refer to it as (say) "homeTeam" together with for awayTeam.

After that you can drag the title column from these two tables, to determine side-by-side what they are called of teams playing in every fixture.

You are on course with MDMA's instructions. Do this:

  1. Create a new query.
  2. Add tblTeam
  3. Add tblTeam again (It'll instantly relabel itself to tblTeam_1 or something like that similar)
  4. Add tblFixture
  5. Join tblFixture.HomeTeam to tblteam.id
  6. Join tblFixture.AwayTeam to tblteam_1.id

alt text

For Access 2007, the simplest option would be to produce aliases for the tables.

  1. 'Show' the table (teams) two times within the associations window
  2. Right click each table and modify its qualities
  3. Within the qualities dialog, alter the alias title (home_team, away_team) for that tables you've proven above.
  4. Go ahead and take PK (team_id) from each alias (home_team, away_team) and relate it for your fittings table:

    (home_team.team_id -> fittings.home_team_id away_team.team_id -> fittings.away_team_id).

The fixture table might have this structure:

team_id INT,
team_type char(1) -- 'A' for Away, 'H' for home

thus, you've two cases of one team, differenced with a flag which informs if it's away or perhaps a home situation.

this really is one option, others might have other design ideas, just wait and find out the other people think