I am pretty new to Access and have a course for creating a football league database (albeit a small one). My problem stems from the fact that I cannot have the Teamid from one table into another table twice in a relationship.
e.g

Table1:
Teamid (key)
TeamName
Venue
Shirts
Shorts
Socks

Table 2: (Fixture)
Matchid (key)
Date
Type (league or cup)
HomeTeam (from Teamid)
AwayTeam (from Teamid)

As Teamid will not be entered from the external table twice into the new one, do I make a drop down list from Teamid and enter it as a different field?
Or do I make different tables for both Home and Away teams?

I am pretty much at the end of my tether, as there aren't many tutorials on this ... and not many books either!!

Any help is greatly appreciated

Thanks in advance

What you are doing seems to be correct:
Have a one (talbe1) to many (table2) relationship with RI enforced (no cascade delete) for both your HomeTeam and AwayTeam fields.
Have a combo taking its values from Table1 in your form foir the user to fill in the Home Team and the same for the GuessTeam.

BTW it is a good practice to avoid space in field names and to pur ID at the end of field names that are foreign keys (AwayTeamID, HomeTeamID).

Add Table1 to the QBE grid TWICE. Link the first instance to HomeTeam and the second instance to AwayTeam. Access will suffix the name of the second instance with "_1". You'll probably want to rename the columns from Table1 so that you don't get confused.

Select ...,Table1.TeamName as HomeTeamName, Table1_1.TeamName as AwayTeamName, ....