MS access SQL query help!

[Ralph Sharett]

I am creating a database for sports league.

I have a table of Leagues, Teams, and Games. I trying to get a listing of the games but I am getting too much data returned. My problem is I am unable to create a query that only returns 1 listing of a game.

My tables are as such:

****tblTeams
PrimaryKey- TeamID
TeamName
ForeignKey- LeagueID

**** tblGames
PrimaryKey- GameID
TeamName
ForeignKey- LeagueID
HomeID --- This is the linked to tblTeams.TeamID These relationships are the root of my problem.
VisitorID --- This is the linked to tblTeams.TeamID

****tblLeagues
PrimaryKey- LeaugueID
LeagueName


I would like to see each game listed once instead of twice and would like to have the team names replaced for the VisitorID and HomeID. I could just enter in the team name but I would like to keep the linked tables.

SELECT DISTINCT Games.GameID, Games.Location, Games.StartDate, Games.StartTime, Games.HomeID, Games.VisitorID, Games.HomeTotal, Games.VisitorTotal, Games.LeagueID, Teams.TeamName
FROM Teams LEFT JOIN Games ON Teams.LeagueID=Games.LeagueID;

Any help would be greatly appriciated.


You can see a picture of the database relationships here at [url]www.wou-wou.com/tablerelationships.htm[/url]

Thanks,

Ralph
[email]ralph_sharett@hotmail.com[/email]

[Elliott]

I would guess (only a guess though) that you need to link the GameID in the Grade table with the GameID in the games table...