Query Query

[perrygascoine]

I have a table called team which contains the following fields...
team_id [autonumber]
team [text]

There are 46 teams each with their own team_id.

I have a table called games and in this table there are many fields. Two of the fields are called Home_id [number] and Away_id [number] which references the ids from the team table.

Now here is the issue I have....

How do I write a query that can show me the names of the teams. For example I can get the query to show me this...

Game1 1 vs 7

What I want is for it to show me....
Game 1 Chelsea vs Barcelona
Chelsea would be team 1 and Barcelona team 7 in the team table

The bit I am confused on is that from the team table the team could go into the games table in either the home_id column or the away_id column.

I am sorry if this is not very clear. I can send the db to someone if that helps....

Perry

 

[honda882000]

have you tried just bringing in two instances of the 'team' table and doing an outer join from games.Home_id = team1.team_id and games.Away.id = team2.team_id?

 

[perrygascoine]

I am really sorry but access is not my speciality. I can do simple queries but get confused with outer joins etc.

Could you please explain what I need to do

 

[honda882000]

-Create a new query.
-When it asks you which tables you want to add, add in the games table and add in the team table 2 times.
-In Design view, drag the games.Home_Id field on top of the team_id field in the first instance of the team table. This will create a join. Double click on the line which now connects these two fields. You should now be able to create an outer join. Choose the option which shows "all records in games...".
-Do the same thing for Away_Id, but use the other instance of the team table.

Does this make sense? Sorry if its a little confusing.

 

[perrygascoine]

That worked a treat - thanks.

I do have one other query....

I want to list the teams and show the games they have played. For example

Chelsea

Liverpool
Man Utd
Arsenal

​

Liverpool

Chelsea
Everton
Spurs

​

The teams would come out of the teams table and the fixtures from the games table.

Once again hope that makes sense