28 Oct 00 16:54
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!