Having trouble pulling results from related tables

[StevenB]

Howdy all,

I have a hockey database, and I'm having some trouble with database relationships and reporting.

The two tables in question look like this (fields in paren):

Team_Info (Team_ID-PK, Team_Name, Team_City, Team_Nickname, Team_Level)

Game_Info (Game_ID-PK, Home_Team, Visiting_Team, Game_Date, Game_Time,
Game_Location, Season, Home_Goals_P1, Home_Goals_P2, Home_Goals_P3,
Home_Goals_OT, ditto Away, ditto Home_Shots and Away_Shots, Game_Type)

There currently is no established relationship between these two tables. However, Game_Info.Home_Team and Game_Info.Visiting_Team have values that are equal to Team_Info.Team_ID. In other words, Home Team and Visiting Team can only be values that are in Team_ID. That limitation is controlled through the data entry screen, not in the tables themselves.

Question 1: Should I establish a relationship between these two tables in the relationship window? If so, what relationship should it be? Link Home_Team and Visiting_Team to Team_ID in Team_Info?

I'm trying to create a report based largely on Game_Info that will give me the schedule for the season. So far, when I display Home_Team and Visiting_Team, I only get the numbers for each team, when what I really want to display is the correct Team_Name from Team_Info based on the values of Team_ID. So, if Home_Team for a given record is "3", and the value of "3" for Team_ID corresponds to the Team_Name "Sabres", I want the report to display "Sabres." But for now, it only displays "3".

Question 2: How can I create this report so that it displays the values from Team_Name and not the numerical values?

Any help would be greatly appreciated!

Thanks!

Steve

 

[katiekat]

I would say that yeah, you have to make a relationship between the two tables. Yeah, i would say that you should link team ID and home and visiting team IDs. This is a cool question. I *think* that if you create a relationship between the home and team id, and visiting and team id, you should be able to get the text values. I think you would do it by creating a query to base the report on. Then if you don't want the Team Id, you don't have to show the number, you can just show the name, and all of it's related game info. I know this is kind of vague, but if you ask more questions, I will try to answer them. :-Q