INLS161-001 | Spring 2018

Class Session: Wednesday Apr 04, 2018

Access: Database Relationships


Preparations for this Session

Read over these

  1. In Access 2007: The Missing Manual, 1st Edition,
    read 5. Linking Tables with Relationships.
  2. Guide to table relationships
  3. It wouldn't hurt to read the Wikipedia entry on Foreign key for background.
  4. Read this linked article again
    • you already read it prior to the first relational database session to have an idea about why relational databases are useful
    • now read it again after pondering the lecture on tables
    • in the - next session we will discuss how and why to relate tables and we will create relationships between them
top/reload prep panel

Microsoft Help

Use a copy of the PK from the one side of a relationship as a FK in the many side of a relationship. It is possible to do this either manually, or by using the lookup wizard to modify the properties of the FK in the table itself.

We will use the lookup wizard method. Do not use this lecture notes page to step through the process for setting relationships for task 5.02. Follow the rubric directions on the task 5.02 page.

Relationships Introduction

Primary and Foreign Keys

A relationship requires the primary key of one table be inserted as a foreign key in a second table.

The link between the two like attributes is the relationship.

  • tables created manually do not have set relationships
  • primary and foreign keys should share the same name and must share the same datatype and field size
  • after you have established a relationship, then you will only need to enter data in one table for the related tables to be updated with the new data. This will become more clear in queries which create dynamic tables of data.

To view/create relationships in Access, select Database Tools > Relationships (or select the Relationships icon on the toolbar).

relationships info

Types of relationships

one-to-one (unusual)

1:1 relationship

In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A. This setup is hardly ever worth the effort. In most cases it causes a system performance overload. However, in some cases, this setup can actually improve performance. This is not information that you need to know about for this course. If you are interested in understanding more about one-to-one relationship setup and how it affects data, check out one-to-one relationships at databaseprimer.com.

one-to-many (most common)

1:Many relationship

in a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A

note that your table properties provide you advice about how to ensure the relationships are based on the proper field properties.

many-to-many (less common than one-to-many, but essential in many cases)

Many:Many relationship

in a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A

in Access, this type of relationship is only possible by defining a third table (called a junction table)

Creating a relationship in Access

You have tables and you have the potential for a relationship, but until you actually link the tables together, the relationship is not yet established

Establish the relationship in the relationship window.

relationship window

Once you have decided which way the relationship goes, create a copy of the Primary Key from the one side of the relationship and place it as Foreign Key in the many side of the relationship

  1. you may need to do some work on the Foreign Key properties to ensure the field is repeatable in the table that is the many side of the one-to-many relationship.
  2. If you were to use the manual method (we will not) you would accomplish this by selecting the Primary Key with the left mouse key and then by dragging the PK field to the Foreign Key field in the related table and then dropping it there
    • a relationship will appear in the Relationship window between the two tables
    • a lot of stuff is going on in the background in Access, but for our purposes, the creation of that line between the two fields establishes the relationship between them

Editing Relationships

Referential integrity

referential integrity is a system of rules that ensure relationships between records in related tables are valid, and that you don't accidentally delete or change related data

for two tables to retain their referential integrity (that is, so that the relationship between them remains healthy and correct), you should always enforce it.

Cascades

you can override the restrictions against deleting or changing related records and still preserve referential integrity by setting the Cascade Update Related Fields and Cascade Delete Related Records check boxes

  • when the Cascade Update Related Fields check box is set, changing a primary key value in the primary table automatically updates the matching value in all related records
  • when the Cascade Delete Related Records check box is set, deleting a record in the primary table deletes any related records in the related table

When you complete the editing of the relationship, your Relationship view will show you the proper relationship between the two tables.

1:Many relationship

Look Up Fields

Lookup lists:

Display values looked up from a related table

Value lists:

Same as a lookup list but consists of a fixed set of values you type in when the lookup is created. A value list should only be used for values that will not change very often and don't need to be stored in a table

Creating a look up field

In Design View.... (note how this example includes descriptions of each field to help the user understand the intent of the field).

lookup wizard in design view

  1. click in the desired field in the "Data Type" column
  2. select "Look Up Wizard"
  3. press Next (let Access decide what to lookup)

lookup wizard dialog box

If you have the field already related as a Foreign Key, you may have to temporarily delete the relationship in the relationship window in order to run the Lookup Wizard. To check if things worked, change views to Datasheet view.

Look Up Tables can be ...

  1. "limited" meaning only data from the look up table can be entered,
  2. or "not limited", meaning a person can enter data that is not in the lookup table (of course this depends on the tables' relationships)

lookup table.limit to list

  1. click the appropriate Field Name
  2. under the Field Properties area, select the "Lookup" tab
  3. for the option "Limit to List", you can select Yes or No.

For this Access Project, you want "Yes" selected

Go to Task 5.02

Instructor

Lawrence Jones

Office hours by appointment.