Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Simple relationships Q - tying one field to multiple others

Simple relationships Q - tying one field to multiple others

Simple relationships Q - tying one field to multiple others

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!



RE: Simple relationships Q - tying one field to multiple others


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.")

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...

RE: Simple relationships Q - tying one field to multiple others

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close