quick query question

[pat67]

I have a table listing the teams in the league. It has a team ID, Name
and location. I have a schedule template that has date, and home and
away. Home and away are numbers.


ID Name Location
1 Team A Bar A
2 Team B Bar B
3 Team C Bar C
4 Team D Bar D

Date Home Away
9/6/11 1 2
9/6/11 3 4



What I want to do is write a query that will sub the team name in
where the team ID is.

Date Home Away
9/6/11 Team A Team B
9/6/11 Team C Team D


How do I do that?

My first thought was to create a match ID the create a query joining
the teams and schedule for home teams and one for away teams. then
joining those two by the match id. My hope was to be able to do it in
one query. I thought about using dlookup, but i figured that would
make the query run really slow.

 

[John W. Vinson]

I have a table listing the teams in the league. It has a team ID, Name
and location. I have a schedule template that has date, and home and
away. Home and away are numbers.


ID Name Location
1 Team A Bar A
2 Team B Bar B
3 Team C Bar C
4 Team D Bar D

Date Home Away
9/6/11 1 2
9/6/11 3 4



What I want to do is write a query that will sub the team name in
where the team ID is.

Date Home Away
9/6/11 Team A Team B
9/6/11 Team C Team D


How do I do that?

My first thought was to create a match ID the create a query joining
the teams and schedule for home teams and one for away teams. then
joining those two by the match id. My hope was to be able to do it in
one query. I thought about using dlookup, but i figured that would
make the query run really slow.

It can indeed be done with one query. What you need to do is create a query
based on the Schedule table, and add the Team table to the query grid TWICE:
once joined by the "Home" field, and the second instanc by the "Away" field.
Access will assign an alias of Team_1 (or something like that) to the second
instance; you can change the alias if you want. For example, assuming your
tables are named Teams and Games, you could use

SELECT Games.[Date], HomeTeam.[Name], AwayTeam.[Name]
FROM (Games INNER JOIN Teams As HomeTeam ON HomeTeam.ID = Games.Home) INNER
JOIN Teams As AwayTeam ON AwayTeam.ID = Games.Away);

Note that Date and Name are both reserved words and can cause real hassles -
Access can confuse them with the builtin Date() function, and every object in
Access has a Name property. I'd change these fieldnames (say to GameDate and
TeamName); if you don't you should always enclose them in [square brackets].
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[pat67]

I have a table listing the teams in the league. It has a team ID, Name
and location. I have a schedule template that has date, and home and
away. Home and away are numbers.

ID Name Location
1  Team A Bar A
2  Team B Bar B
3  Team C Bar C
4  Team D Bar D

Date       Home Away
9/6/11         1        2
9/6/11         3        4

What I want to do is write a query that will sub the team name in
where the team ID is.

Date          Home           Away
9/6/11      Team A     Team B
9/6/11      Team C     Team D

How do I do that?

My first thought was to create a match ID the create a query joining
the teams and schedule for home teams and one for away teams. then
joining those two by the match id. My hope was to be able to do it in
one query. I thought about using dlookup, but i figured that would
make the query run really slow.

It can indeed be done with one query. What you need to do is create a query
based on the Schedule table, and add the Team table to the query grid TWICE:
once joined by the "Home" field, and the second instanc by the "Away" field.
Access will assign an alias of Team_1 (or something like that) to the second
instance; you can change the alias if you want. For example, assuming your
tables are named Teams and Games, you could use

SELECT Games.[Date], HomeTeam.[Name], AwayTeam.[Name]
FROM (Games INNER JOIN Teams As HomeTeam ON HomeTeam.ID = Games.Home) INNER
JOIN Teams As AwayTeam ON AwayTeam.ID = Games.Away);

Note that Date and Name are both reserved words and can cause real hassles -
Access can confuse them with the builtin Date() function, and every object in
Access has a Name property. I'd change these fieldnames (say to GameDate and
TeamName); if you don't you should always enclose them in [square brackets].
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

Thanks. Let me try that

 

[pat67]

It can indeed be done with one query. What you need to do is create a query
based on the Schedule table, and add the Team table to the query grid TWICE:
once joined by the "Home" field, and the second instanc by the "Away" field.
Access will assign an alias of Team_1 (or something like that) to the second
instance; you can change the alias if you want. For example, assuming your
tables are named Teams and Games, you could use

SELECT Games.[Date], HomeTeam.[Name], AwayTeam.[Name]
FROM (Games INNER JOIN Teams As HomeTeam ON HomeTeam.ID = Games.Home)INNER
JOIN Teams As AwayTeam ON AwayTeam.ID = Games.Away);

Note that Date and Name are both reserved words and can cause real hassles -
Access can confuse them with the builtin Date() function, and every object in
Access has a Name property. I'd change these fieldnames (say to GameDate and
TeamName); if you don't you should always enclose them in [square brackets].
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com-Hide quoted text -

- Show quoted text -

Thanks. Let me try that- Hide quoted text -

- Show quoted text -

that worked. thanks