You might think of relational data as a jigsaw puzzle. The
pieces mean little and give few clues to the finished product that’s revealed
only after you painstakingly build the puzzle, piece by piece. Relational data
is like that puzzle, except for one thing: Access knows exactly which record
matches another, and pulling the pieces together is almost instantaneous. Relationships
make it all happen behind the scenes.
Most of the time, the default relationship, one-to-many,
works fine. But when the one-to-many is inadequate, you must determine whether
the relationship is a one-to-one or a many-to-many.
The one-to-one is the least common and is easily accomplished by setting a
unique index on the foreign key field. The many-to-many relationship requires
more work, as you’ll learn in this article.
The three types of relationships
A relationship is as association between two tables that enables
Access to pull together related data that’s stored in multiple tables. There
are many types of relationships, and Access supports three:
- One-to-many is the most common
relationship, in which the primary key value matches none, one, or many
records in a related table. - One-to-one is probably the least
common of the three, where a primary key value matches only one (or no)
record. These relationships are almost always forced by business rules and
seldom flow naturally from the actual data. - Many-to-many occurs
when both tables contain records that are related to more than one record.
Access doesn’t directly support a many-to-many relationship, so you must
create a third table: an “associate table.” It contains a
primary key and a foreign key to each of the data tables. After breaking
down the many-to-many relationship, you have two one-to-many relationships
between the associate table and the two data tables.
Spotting a many-to-many
The key to spotting a many-to-many relationship is knowing your
data—nothing takes the place of a keen understanding of how all that data fits
together to produce the needed results. The more familiar you are with the
data, the easier the many-to-many relationship is to spot
during the design process. The ones you miss should pop up during the
development process as you create prototype forms. For instance, after the
initial design process for a simple database to track books, you might have the
table specifications listed in Table A.
|
Table A |
|
Besides the primary key index, each table has a unique index:
- Books:
TitleISBN - Authors:
LastNameFirstName
Figure A shows the one-to-many relationship between Books and
Authors.
|
Figure A |
 |
| Access assigns the default one-to-many relationship between the two tables. |
Figure B
represents a typical data entry form. (We used the AutoForm wizard and based
the form on the Authors table.) The wizard automatically represents the
one-to-many relationship in a main/subform arrangement, where the main form
displays the records on the one side
of the relationship (the Authors table) and the subform displays the many side (the Books table). The form
looks as though it can handle multiple authors for the same book, but try
entering the following book records:
- Upgrader’s
Guide to Microsoft Office System 2003, 0-7897-3176-2 by Mike Gunderloy and
Susan Harkins - Automating
Microsoft Access 2003 with VBA, 0-7897-3244-0 by Mike Gunderloy and Susan
Harkins
Figure B shows
the record for Susan Harkins after entering the books for Mike Gunderloy. When
you try to save the record, Access displays an error because the book entries
in the subform violate the table’s unique index. (You entered those books when
you entered Mike Gunderloy’s record.)
As you can see in Figure
C, Access stored both author names and both book records, but the books are
related only to Mike Gunderloy (AuthorIDFK value 1). There’s no way to relate
these two books to more than one author. Without the unique index on the Title
and ISBN fields, Access stores each book record twice, but repeating the data
violates the First Normal Form of normalization—there should be only one record
for each book.
|
Figure B |
 |
| You might think this form could handle books with more than one author…. |
|
Figure C |
 |
| …But Access can’t relate the books to a second author. |
Putting the relationships to work
Accommodating the many-to-many relationship is a simple four-step
process:
- Delete
the existing relationship between the two tables. - Create
the associate table and include a foreign key field for both of the related
data tables. - Create
a one-to-many relationship between each of the data tables and the
appropriate foreign key in the associate table. - Delete
the foreign key field in the many
table of the original one-to-many relationship.
To apply these steps to the book tracking example, start by opening
the Relationships window and deleting the relationship between Books and
Authors. (Right-click on the join line between the two tables and choose Delete.)
Next, create a new table named AuthorsBooksmm, with these three fields:
- AuthorBookID
(AutoNumber) - BookIDFK
(Number) - AuthorIDFK
(Number)
(Adding the suffix mm
to the table name is a good way to identify the table as an associate table; mm stands for many-to-many.)
Now, assign a unique multifield index to both the BookIDFK
and AuthorIDFK fields. Open the Relationships window, add AuthorsBooksmm, and create
the following one-to-many relationships, as shown in Figure D:
- Books:BookID
to AuthorsBooksmm:BookIDFK - Authors:AuthorID to AuthorsBooksmm:AuthorIDFK.
Close the Relationships window and save the changes. Then,
open the Books table and delete the AuthorIDFK. Close and save the Authors table.
|
Figure D |
 |
| Create the one-to-many relationships between the two data tables and the associate table. |
As a general rule, when accommodating a many-to-many
relationship via an associate table, you’ll need to base forms on a query:
- Add
all the non-key fields you may want to modify or may need from both the many and the one table. - Add
the foreign key that represents the one
side from the associate table.
To continue our example, build the query shown in Figure E and save it as AuthorsBooks.
Currently, the query displays no records because there are no records in the
associate table to link the two books to either author. Open AuthorsBooksmm and
enter the two records shown in Figure F,
which relate both book records to the author Mike Gunderloy.
|
Figure E |
 |
| Base the new data entry form on this query. |
|
Figure F |
 |
| These two records will link both books to Mike Gunderloy. |
Use the Form Wizard (not the AutoForm wizard) to base a new
form on the AuthorsBooks query. Include all the fields and choose the subform
view, as shown in Figure G. The
completed form correctly displays the author Mike Gunderloy as an author for
both book records.
|
Figure G |
 |
| The Form Wizard recognizes the relationships between the three tables. |
At this point, you could easily add Susan Harkins to both
book records, but this is where the situation gets a little sticky, especially
if you’re converting a relationship where data already exists. Susan Harkins
already exists in the Authors table; therefore, entering Susan Harkins in the
subform will generate a duplicates error. Without the unique index, Access
allows you to enter Susan Harkins, creating a duplicate record.
A simple fix is to turn the foreign key field in the subform
into a combo box and then choose an existing author from the list or enter a
new one. To accomplish this task, open the completed form in Design view and
change the foreign key field’s bound control (AuthorIDFK) to a combo box. (Right–click
the control, choose Change To, and then select Combo
Box.) Set the combo box control’s Row Source property to the following SQL
statement:
SELECT AuthorID, LastName, FirstName FROM Authors ORDER BY LastName
In addition, set the Column Count property to 3. Return to
Form view and display the control’s drop-down list, as shown in Figure H. Select Susan Harkins and
repeat for the second book record.
|
Figure H |
 |
| Choose an author from the new combo box control’s drop-down list. |
Open all three tables, and you’ll see that each author and
book appears just once. The associate table relates both authors to both books,
which is easily illustrated by running the AuthorsBooks query shown in Figure I.
|
Figure I |
 |
| The associate table maintains these relationships. |
Don’t hide from your relations
Don’t let a many-to-many relationship confound you. Apply
the simple guidelines presented in this article to add an associate table and
maintain those relationships correctly. Then, simply take advantage of them
once they’re correctly in place.
Susan Sales Harkins is
an independent consultant and the author of several articles and books on
database and Web technologies. Her most recent books are Automating
Microsoft Access 2003 with VBA, Upgrader’s
Guide to Microsoft Office System 2003, ICDL
Practice Questions Exam Cram, ICDL
Exam Cram 2, Absolute Beginner’s
Guide to Microsoft Access 2003, and Absolute
Beginner’s Guide to Microsoft Access 2002,
with Mike Gunderloy, all published by Que. Currently, Susan volunteers as the
publications director for Database Advisors.
