Argh. Need help with query looking up twice from same table

[Quixotic1]

Hey all:

Been a while since I've tried my hand at an Access DB, and to brush up
my weak skills, I'm putting together what I hoped would be a rather
simple DB for myself. And, well, ARGH!

I'm working on an NFL schedule and standings DB. I've got two tables:

Table "TblTeam" has:
TeamNum
TeamName
etc.

Table "TblSched" has:
WeekNum
AwayTmNum
HomeTmNum
GmDate
Network
AwayScore
HomeScore
etc.

I'm working on queries to print out basically Weekly Schedule with
Kickoff times showing both teams playing, and Team Schedule with
Kickoff times showing teams playing.

So, I'm trying to convert the AwayTmNum to the associated TeamName and
the HomeTmNum to the associated TeamName in a query.

When I set up the query in the grid with a new expression field
(AwayTeam) I set it TblTeam:TeamNum = TblSched:AwayTmNum and ask for
the TeamName.

No problem

But, when I add another expression to the grid where TblTeam:TeamNum =
TblSched:HomeTmNum and ask for the TeamName, I get nothing.

OK. I get that. That's set up an OR, and none exist.

So, I set the criteria to the second line for the Home Team, but what
I end up getting is 32 lines, instead of 16 lines. Where Line 1 shows
the same team name for both home and away. Line 2, showing the same
game, shows the same team name for both home and away, but, it's the
other team... get it?

So, how can I rewrite this so that I essentially replace the Team
numbers with the team names in flat table, giving me the 16 games for
each week?

I'm starting to think I've set this up all wrong. But, I can't think
of any other way to connect 32 teams playing each other... And, I
can't imagine that two identical tables are necessary - one for Away
teams, and one for Home teams.

I considered just using teamnames, and avoiding this conversion from
team number, however, I'll run into the same problem when I start
querying for AwayScore and HomeScore.

Thoughts please?

Quixotic1

 

[LeAnne]

Hi,

Easy! Put your Teams table in the query _twice_. Join
tblTeams.TeamNum=tblSched.HomeTmNum, and join
tblTeams_1.TeamNum=tblSched.AwayTmNum. The query would look something
like:

SELECT tblTeams.TeamName AS HomeTeam, tblTeams_1.TeamName AS AwayTeam,
tblSched.DatePlayed
FROM tblTeams INNER JOIN (tblTeams AS tblTeams_1 INNER JOIN tblSched ON
tblTeams_1.TeamID = tblSched.AwayTeamNumber) ON tblTeams.TeamID =
tblSched.HomeTeamID;

hth,

LeAnne

 

[Quixotic1]

Thanks LeAnne,

Wow, I feel like an idiot. That makes life so much easier. And, it
helped me re-think the scores table, too. Works much better, now. I'm
sure I've used that "trick" before, but it's been a few years since I
last built any database. Just trying to revitalize my old skill set.
I've been disabled now for nearly 4 years.

Thanks again. I appreciate that you are all here to help us old
newbies out.

Dave
Quixotic1