Access Basics:
Table Relationships

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

You finally have two tables that have a relationship. The relationship was created by the Lookup Wizard in the previous lesson.

Access will recognize matching fields between tables in some cases. In other situations you will have to show Access what fields to match and what kind of relationship it is: One-to-Many, Many-to-Many, or One-to-One.

Examples below are from starwars.mdb.

Relationships Window

Access provides a special window for viewing relationships. No tables are shown automatically. You must pick which ones you want to see.


Relationships window with its toolbar

Relationships window with its toolbar

Join lines connect the matching fields between tables. A line with a 1 at one end and the infinity symbol symbol for infinity at the other indicates a One-to-Many relationship for which Access will enforce referential integrity. That means that Access will make sure that you do not delete records that are referred to by records in another table.

Example of referential integrity: Access would not let you delete a supplier from the Suppliers table if that supplier's ID was included in Star Wars Collectibles table.

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics Arrow: subtopic open
    InterfaceTo subtopics
    Getting StartedTo subtopics  
    Access ObjectsTo subtopics
    Relationships Arrow: subtopic open
    Icon: StepTable Wizard
    Icon: StepLookup Wizard
    Icon: StepTable Relationships
    ExercisesTo subtopics

Project 3: Tables & Queries

Project 4: Forms & Reports


Relationships Toolbar

Toolbar: Relationships

The Relationships window has its own toolbar, with three new buttons:
 Button: Show Tables (2003) Show Table = opens a dialog where you can pick which tables and queries to show in the Relationships window.
 Button: Show Direct Relationships (2003) Show Direct Relationships = Shows the relationships that have been defined for the selected table.
Button: Show All Relationships (2003) Show All Relationships = Shows all of the relationships that have been defined in the database.

Icon Step-by-Step 

Step-by-Step: Relationships

 Icon Step-by-Step

What you will learn:

to view a relationship between two tables
to delete a relationship
to create a relationship between two tables
to print relationships
to view a subdatasheet
to see referential integrity in action

Start with: mytrips.mdb from previous lesson

View Relationships

There is a special window for viewing Relationships. In the previous lesson, the Lookup Wizard said it needed to create a relationship.  Let's look at that relationship.

  1. Menu: Tools | RelationshipsFrom the menu select  Tools | Relationships...
    The Relationships window opens. It may be blank or it may show the existing relationship created by the Lookup Wizard.
    Blank relationships window  or Relationships: Trips and Travel Agents
    If the window is blank, you can tell Access which tables to display  in the Relationships window.
    For practice, let's look at how to create a relationship.
  2. If the window is not empty, click on each table in the Relationships window and press the Delete key.
    TipWhen you delete a table from the Relationships window, you do not delete the table itself. You do not delete any relationships either. You have changed only what is shown in the window.
  3. Dialog: Show TableClick on Button: Show Tables (2003) the Show Table button on the Relationships toolbar.
    A dialog appears from which you can choose tables and queries to put in the Relationships window.
  4. Select the table Travel Agents and click the Add button.
    The Relationship window now shows a list of all the fields in the table.
    If necessary, drag the Show Table dialog to a position where you can see the list in the Relationships window.
  5. Select the table Trips in the Show Tables dialog and click the Add button.
    When the Trips table appears in the Relationships window, a line appears that connects the two tables.

    Relationships: Trips and Travel Agents

  6. Close the Show Table dialog by clicking the Close button.
  7. If necessary, drag the bottom edge of the Trips table down until you can see the field AgentID.

    The field AgentID from Travel Agents is connected to the field AgentID in Trips. This is the relationship that the Lookup Wizard created! Once the tables were displayed, Access already knew about the relationship.

    TipFields must have the same data type to be used to create a relationship between tables. Usually the field is the primary key in one table.

  8. Message: Do you want to save changes to the layout of 'Relationships'?Close the Relationships window.
    A message box appears asking if you want to save the changes to the layout.
  9. Click Yes.

    Access will now remember which tables were showing and their sizes and arrangement for the next time you view Relationships.

Delete a Relationship

Why would you want to delete a relationship? If you need to make changes to the table design, Access may not let you while there is a relationship to another table. Of course, you need to be careful that the relationship will still work after you make your changes!

  1. Relationships: join line selectedIn the Relationships window, click on the line connecting the two tables. The line gets thicker.
  2. Message: are you sure you want to delete?Press the Delete key or click the Delete button on the toolbar.
    A message box appears asking if you are sure.
  3. Relationships window after deleting join lineClick Yes.
    The line vanishes but the tables are still displayed.

Create a Relationship Manually

To create a relationship manually is a simple drag and drop action. You will create a new join between the two tables, but it will not be quite like the earlier one.

  1. Drag the field AgentsID from the Travel Agents table and drop it on the AgentsID field in the Trips table. (Be careful to drop on the correct field!)

    Dialog: Edit RelationshipsThe Edit Relationships dialog appears, showing the table names and the field names.
  2. Dialog: Join Properties - choice 2 selectedClick the button Join Type...
    The Join Properties dialog appears. There are three choices for how records will be matched up in a query.
    • Choice 1 will show only agents that have trips and trips that have agents. There must be a match in the other table!
    • Choice 2 will show all of the agents and match them with the trips that share their agent ID number. It will show agents that do not have any trips. It will not show trips that do not have an assigned agent.
    • Choice 3 will show all of the trips and match them with the agents. It will show trips that do not have an assigned agent. It will not show agents who don't have any trips yet.
  3. Click on the 2nd choice = Include ALL records from 'Travel Agents' and only those records from 'Trips' where the joined fields are equal."
    This is the right choice since each travel agent can arrange more than one trip, but each trip should have only 1 travel agent.
  4. Click on OK to close the Join Properties dialog.
  5. Dialog: Edit Relationships - section Enforce Referential IntegrityIn the Edit Relationship dialog, click in the box Enforce Referential Integrity. This choice tells Access not to allow changes that would break the connection between these tables.
    Leave the other boxes unchecked this time.
  6. Relatonships window: One to Many relationshipClick on the button Create.
    The line between the tables reappears, but this time it has symbols on each end that tell you it is a One-to-Many join and that referential integrity will be enforced
  7. Click on Button: Save (2003) the Save button on the toolbar to save this layout.

Print Relationships

The Print button on the Relationships toolbar is grayed out. Unexpectedly, the File menu does contain a command for printing this window, Print Relationships.

  1. Menu: File | Print RelationshipsClick the File menu to open it and select  Print Relationships...
    This command does not show unless the Relationships window is active.
    Access creates a report that show the tables and joins that are in the Relationships window.

    Print Preview: Relationships window for My Trips

  2. Click the Print button on the Print Preview toolbar to print this page.
  3. Close the report. Choose No when asked if you want to save the report.
  4. Icon: KeyboardPress the F11 key to switch to the Database Window. (A useful shortcut when that window is covered up by others!)

View/Edit Subdatasheet

A table that is in a One-to-Many or One-to-One relationship has a new feature. Each record on the "One" side has a subdatasheet that shows the related records from the other table.

  1. Open the Travel Agents table.
  2. Datasheet with subdatasheets showingClick the expand button Button: Expand at the left end of the record for Jorge Martinez.
  3. Repeat for Hector Chavez.
    A subdatasheet opens for each travel agent. It shows the related records in the Trips table. This feature is available for the records in the "One" side of a "One-to-Many" relationship.
    You can actually edit the Trips record from here!
  4. Saved edit from inside subdatasheetIn the subdatasheet for Hector Chavez, change the TripName to Italy tour and press the down arrow on the keyboard to move to the next record. Your change is saved automatically.
  5. Collapse each of the subdatasheets by clicking the Button: Collapse collapse button at the left of the record.

Menu: Format | Subdatasheet | Expand AllTipHow to see or hide all subdatasheets at once:
   Format | Subdatasheets  Choices are Expand All, Collapse All, Remove.  The Subdatasheet item will not be available if the focus is in a subdatasheet.

Test Referential Integrity

Let's see what Access will do now that it must enforce referential integrity on your new join.

  1. Table Datasheet View: record for Hector Chavez selectedSelect the record for Hector Chavez by clicking the record selector at the left end of the record.
  2. Message: The record cannot be deleted or changed because table 'Trips' includes related records.Press the Delete key to delete the record.
    A message appears. Access will not let you delete the record because it is related to records in the Trips table.
    TipChanging a related record: You can change anything about a record that is related to records in another table, like Hector Chavez, except the field that is shared. In this exercise the shared field was an AutoNumber field, which you cannot change anyway!
    TipDeleting an unrelated record from a related table: Access will allow you to delete a record in a table that has a relationship as long as that particular record is not referred to by another table.
  3. Close Access.

LessonsWorking with Databases Previous Page Next Page

Teachers: Request permission to use this site with your class
Copyright 1997-2012 Jan Smith   <>
All Rights Reserved

Icon: DonwloadIcon: CDWant a local copy with no ads? - Download/CD

Want to help?

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~

Last updated: 30 Apr 2012