26
Lecture 3 - Building Table Relationships
By now, you have set up the tables you need for your
database, and created fields for those different tables.
Relationships
provide
Access 2007 with the means to bring that information together for you when you
need it.
.
Establishing Relationships
To establish a relationship between tables:
Click the
Relationships
command in the
Show/Hide
group on the
Database
Tools
tab in the
Ribbon
.
NOTE: Tables must be closed in order to establish relationships.
Relationships Command
When the
Show Table
dialog box appears:
Select each table name and click
Add
for the tables you want to relate.
When you are done,
Close
the Show Table dialog box.
27
Show Table Dialog Box
You should now see a
relationship map
that contains all the tables that were
selected.
Relationship Map
Moving Tables in the Relationship Map
To move a table that appears in the
relationship map
:
Place your mouse over the table you want to move.
Hold down the left mouse button, and drag the table to a new location.
Release the mouse button to drop the table in its new place.
Understanding the Relationship Map
28
The relationship map lists all of the tables that were selected to relate, and all
of the fields that were set up for that table previously. Notice that the first field
has a key icon next to it. This is the
Primary Key
for the table.
Primary
Keys
Primary and Foreign Keys
A
Primary Key
is the first field in each table of the database. You may recall
that this field auto numbers by default, so that every record in the table has its
own unique number to identify it. Access uses this number to quickly pull
information together for you when you run queries or reports, which are
covered later.
In the example above, the primary key for the Customers table is
Customer ID, the primary key for the Orders table is Order ID, and the
primary key for the Books table is Book ID.
A
Foreign Key
is a field that is the Primary Field in its own table, but that
shows up in another table. If you look closely at the Orders table, the fields
Customer ID
and
Book ID
appear there, as well as in their own respective
tables. These fields are the primary key in their own tables, but in the Orders
table, they are considered
Foreign Keys
.
Foreign
Keys
Relating Tables
There are a few ways to establish relationships between tables:
29
Using the
Edit Relationships
command located on the
Design
tab of the
Ribbon
Using the
Drag and Drop
method
Both methods give you the same end result, but the
Drag and Drop
method is
much easier and saves you several steps.
Relating Tables with the Drag and Drop Method
It is easy to relate tables from the relationship map:
Select a field name from one table by holding down the left mouse button.
Relationship Map
Drag the field name from the one table to the other table in the desired
relationship.
Drop the first field name onto the field name that you want to relate by
releasing the left mouse button.
In the example above, we selected the Book ID field from the Books table,
and dragged and dropped it on the Book ID field in the Orders table.
The
Edit Relationships
dialog box appears.
30
Edit Relationships Dialog Box
Select the
Enforce Referential Integrity
option. This option is explained in
detail below.
Click
Create
.
Understanding Types of Relationships
Access 2007 allows for several different types of relationships. These include:
One to One
One to Many
Many to Many
The relationship type you will come across most frequently, and the one created in
our bookstore scenario, is the
One to Many
relationship.
One to Many
The
One to Many
relationship means that data for that field will show up a single
time in one table, but many times in the related table.
For example, let's look at one of the book titles in our bookstore. The Book ID for
that book should appear only once in the Books table, because that table lists
every title that we stock. But it will probably appear many times in the Orders table,
because we hope that it gets ordered by many people many times.
31
The symbols for the
One to Many
relationship look like this:
One to
Many Relationships
Enforcing Referential Integrity
In the
Edit Relationships
dialog box, an option to
Enforce Referential Integrity
appears.
You should click
Enforce Referential Integrity
to make sure that we NEVER have
an order for a book that doesn't appear in our Books table. Selecting this option
tells Access to check for these things when someone is working with your data
records.
Editing Existing Relationships
Access 2007 allows you to edit relationships that already exist. This can be done
using the
Edit Relationships
command on the
Ribbon
. However, a much simpler
way is to simply
double click
on the link that appears in the relationship map.
Either method brings up the
Edit Relationships
dialog box, where you can change
your settings.