Simple relationships Q - tying one field to multiple others 1

[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

 

[MoGryph]

Okay....

Let's start a at the end first, because I like to work backwards. hehe.

Getting the reports to not say "3" and instead, say "Sabres" is really something that you can do through the reports, but can more easily be done through the original setup of the Table (specifically, the Game_Info table).

When you set up the fields "Home_Team" and "Visiting_Team", instead of typing "Number" as a data type, type Lookup Wizard. Tell the wizard that you want the first option ("I want the lookup column to look up the values in a table or query.&quot

Click next, and choose the "Team_Info" table. Click Next.
Choose the fields "Team_ID" and "Team_Name" (ie, move them to the selected fields section) and click Next.
Make sure the recommended "Hide key column" is selected.

This actually just takes care of formatting the "Default" control that is displayed on the reports.
You can do this manually as well, if you want to. Here's what you do:
Set the field type to Number.
Click the "Lookup" tab on the bottom.
Enter the following properties:
Display Control "Combo Box"
Row Source Type "Table/Query"
Row Source: "SELECT DISTINCTROW [Team_Info].[Team_ID], [Team_Info].[Team_Name] FROM [Team_Info];"
Bound Column: 1 *** Which "column" is bound, in this case "Team_ID"
Column Count: 2 *** Number of columns (Team_ID, Team_Name)
Column Heads: No
Column Widths: 0;1 *** Column widths. hide column one by setting it to width 0.
The rest can pretty much be left as default.

If you create your report now, using the wizard, you should now get "Sabres", because you've told Access that you want the default control that is created, to be a combobox, hiding the first field. (confusing, sorry- best I can explain it).

Now, to the first part of your question-
Relationships? Absolutely. Set them up. And this one is kind of one that I myself took awhile to figure out.
For this specific case, pull up your Relationships window, and Add Tables - "Game_Info" and "Team_Info". THEN...... Add table "Team_Info" AGAIN! Yes, add it twice. It will now come up as "Team_Info_1". This is GOOD- you want it.

Create a link from [Team_Info].[Team_ID] to [Game_Info].[Home_Team] and another link from [Team_Info_1].[Team_ID] to [Game_Info].[Visiting_Team].

Team_Info_1 acts as a second copy of the original team table. This way, you can access the seperate "RecordSet", and get good data.

That should do it for you...

 

[StevenB]

Great info, thanks! It's working much better...