Accommodating a many-to-many relationship in Access

You can take advantage of many-to-many relationships in your Access databases; it just requires a little extra work up front. This walk-through demonstrates a four-step process for creating an associate table to handle the relationship.

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

Table Name Field Name Data Type
Books BookID (PK) AutoNumber
Title Text
AuthorIDFK Num
Authors AuthorID (PK) AutoNumber
LastName Text
FirstName Text

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:

  1. Delete the existing relationship between the two tables.
  2. Create the associate table and include a foreign key field for both of the related data tables.
  3. Create a one-to-many relationship between each of the data tables and the appropriate foreign key in the associate table.
  4. 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.