Section 5.3. More Exotic Relationships | Access 2007[c] The Missing Manual

5.3. More Exotic Relationships

As you learned in Section 5.2, a one-to-many (a.k.a. parent-child ) relationship that links a single record in one table to zero, one, or more records in another table is the most common relationship. A single manufacturer could be linked to one bobblehead, several bobbleheads, or no bobbleheads at all.

FREQUENTLY ASKED QUESTION
Refreshing a Lookup

I just added a record, but it doesn't appear in my lookup. Why not ?

Access fills in your lookup lists when you first open the table. For example, when you open the Dolls table, Access gets a list of manufacturers ready to go. However, sometimes you might have both the table that uses the lookup and the table that provides the lookup data open at the same time. In this situation, the changes you make in the table that provides the lookup won't appear in the table that uses the lookup.

To see how this works, open both the Dolls and Manufacturers tables at once. (They'll appear in separate tabs.) In the Manufacturers table, add a new manufacturer. Now, switch back to the Dolls table and try using the ManufacturerID lookup. You'll notice that the lookup list doesn't show the new record.

Fortunately, there's an easy to solution. You can tell Access to refresh the lookup list at any time by choosing Home Records Refresh All. Try that out in the Dolls table, and youll see the updated list of manufacturers appear in the lookup.

Along with one-to-many relationships, there are two subtly different types of relationships: one-to-one relationships and many-to-many relationships. You'll learn about both in the following sections.

5.3.1. One-to-One Relationship

A one-to-one relationship links one record in a table to zero or one record in another table. People sometimes use one-to-one relationships to break down a table with lots of fields into two (or more) smaller tables.

A Products table may include detailed information that describes the product and its price, and additional information that describes how it's built. This information's important only to the people in the engineering department, so you may choose to split it into a separate table (named something like Products-Engineering). That way, sales folks don't need to think about it when they're making an order. Other times, you might break a table into two pieces because it's simply too big. (Access doesn't let any table have more than 255 fields.)

You create a one-to-one relationship in the same way you create a one-to-many relationshipby dragging the fields in the Relationships tab (Figure 5-15). The only difference is that the linked fields in both tables need to be set to prevent duplicates. This way, a record in one table can (at most) be linked to a single record in the other table.

Note: A field prevents duplicates if it's set as the primary key for a table (Section 2.4), or if it has an index that prevents duplicates (Section 4.1.3).

Figure 5-15. When you link two fields that don't allow duplicates (and you have the Enable Referential Integrity option switched on), Access realizes that you're creating a one-to-one relationship. Access places the number 1 at each side of the line to distinguish it from other types of relationships. In this example, the ID column in the Products table and the ID column in the ProductsEngineering table are both primary keys of their respective tables, so there's no way to link more than one record in ProductsEngineering to the same record in Products.

WORD TO THE WISE
Approach One-to-One Relationships with Caution

One-to-one relationships are extremely rare in Access. Usually, features like column hiding (Section 3.1.4) and queries (Chapter 6) are better choices if you want to see only some of the fields in a table.

Splitting a table into two pieces complicates the design of your database, and you'd generally do it only if you have other reasons to separate the tables. Some possible examples include:

The two parts of the table need to be placed in separate databases (Section 18.2) so that different people can copy them to separate computers and edit them independently.
You want to stop prying eyes from seeing sensitive data. One way to do this is to put the information that should be secure into a separate table, and put that separate table in a different, more secure database file.
You have a table that stores huge amounts of data, like an Attachment field (Section 2.3.8) with large documents. In this case, you might get better performance by splitting the table. You might even choose to put one half of the table in a separate database (Section 18.2).
Some of the data in your table's optional. Rather than include a lot of blank fields, you can pop it into a separate table. If you don't need to include this information, then you don't need to add a record to the linked table.

If you don't have these requirements, then you're better off creating a single large table.

5.3.2. Many-to-Many Relationship

A many-to-many relationship links one or more records in one table to one or more records in another table. Consider a database that tracks authors and books in separate tables. Best-selling authors don't stop at one book (so you need to be able to link one author to several books). However, authors sometimes team up on a single title (so you need to be able to link one book to several authors). A similar situation occurs if you need to put students into classes, employees into committees , or ingredients into recipes. You can even imagine a situation where this affects the bobblehead database, if more than one manufacturer can collaborate to create a single bobblehead doll.

Many-to-many relationships are relatively common, and Access gives you two ways to deal with them.

5.3.2.1. Junction tables

Junction tables are the traditional approach for dealing with many-to-many relationships, and people use them throughout the database world (including in industrial-strength products like Microsoft SQL Server). The basic idea's that you create an extra table that has the sole responsibility of linking together two tables.

Each record in the junction table represents a link that binds together a record from each table in the relationship. In the books and authors database, a single record in the junction table links together one author with one book. If the same author writes three books, then you need to add three records to the junction table. If two authors work on one book, then you need an additional record to link each new author.

Suppose you have these records in your Authors table:

Table 5-6.

ID	FirstName	LastName
10	Alf	Abet
11	Cody	Pendant
12	Moe	DeLawn

And you have these records in your Books table:

Table 5-7.

ID	Title	Published
402	Fun with Letters	January 1, 2007
403	How to Save Money by Living with Your Parents	February 24, 2008
404	Unleash Your Guilt	May 5, 2007

Here's the Authors_Books table that binds it all together:

Table 5-8.

ID	AuthorID	BookID
1	10	402
2	11	403
3	12	403
4	11	404

Authors_Books is a junction table that defines four links. The first record indicates that author #10 (Alf Abet) wrote book #402 (Fun with Letters). As you traverse the rest of the table, you'll discover that Cody Pendant contributed to two books, and two authors worked on the same book (How to Save Money by Living with Your Parents).

Tip: The junction table often has a name that's composed of the two tables it's linking, like Authors_ Books.

The neat thing about a junction table is that it's actually built out of two one-to-many relationships that you define in Access. In other words, the junction table's a child table that has two parents. The Authors table has a one-to-many relationship with the Authors_Books table, where Authors is the parent. The Books table also has a one-to-many relationship with Authors_Books, where Books is the parent. You can define these two relationships in the Relationships tab to make sure referential integrity rules the day (Figure 5-16).

Figure 5-16. The many-to-many relationship between Authors and Books is really two one-to-many relationships that involve the Authors_Books table. Once you've defined these relationships, you can't link to an author or book that doesn't exist, and can't delete an author or book that has a record in the Authors_ Books table.

Although junction tables seem a little bizarre at first glance, most database fans find that they quickly become very familiar. As with the one-to-many relationships you used earlier, you can create lookups (Section 5.2.5) for the AuthorID and BookID fields in the Authors_Books table. However, you'll always need to add the Authors_Books record by hand to link an author to a book.

5.3.2.2. Multi-value fields

Up until Access 2007, junction tables were the only option for many-to-many relationships. But to support the SharePoint integration features (Chapter 21), Access 2007 adds a new feature: multi-value fields .

As its name suggests, a multi-value field can store more than one value. This capacity neatly solves the problem of many-to-many relationships. The trick's to configure the linked field in the child table as a multi-value field. Reconsider the authors and books example. Without the junction table, you'd need to add an AuthorID column to the books table to indicate which author wrote a given Book:

Table 5-9.

ID	Title	Published	AuthorID
402	Fun with Letters	January 1, 2006	10
403	How to Save Money by Living with Your Parents	February 24, 2005	11
404	Unleash Your Guilt	May 5, 2006	11

But an ordinary field holds a single value. Thus, this table can indicate only one of the two authors for book #403.

However, if you change AuthorID to allow multiple values, you can enter a list of authors, like this:

Table 5-10.

ID	Title	Published	AuthorID
403	How to Save Money by Living with Your Parents	February 24, 2005	11, 12

Behind the scenes, a multi-value field actually uses a junction table. However, Access hides that detail from you, which makes it a bit easier to link related records.

In order to create a multi-value field, you need to use a lookup. As you've already seen (Section 5.3), you can choose to turn on this option in the last step of the Lookup wizard. Alternatively, if you already have a lookup in a field, you just need to make one minor modification. Open the table in Design view, choose the field that has the lookup (like ManufacturerID), and then, in the Field Properties section, click the Lookup tab. Look for the Allow Multiple Values property, and change it from No to Yes.

Note: Once you change your field to support multiple values, you can't switch back.

Figure 5-17 shows a multi-value lookup list in action.

Figure 5-17. This lookup list uses checkboxes, because it's on a multi-value field. You can select several values for a single record by checking more than one item. So you can indicate that a single doll was created by a two-manufacturer partnership.

Multi-value fields are available only if you're using the new .accdb database format (Section 1.2.2). You can't use them with an .mdb file (a database that was created in Access 2003 and hasn't been converted yet).

Multi-value fields also cause headaches if you want to upsize your database to SQL Server (as described in Chapter 20), because SQL Server doesn't support them. So if there's a possibility that you'll need to share your database with lots of people (say, in a large company), and you might move your data to a high- powered SQL Server database someday, avoid multi-value fields.

Note: Multi-value fields don't pose a problem if you want to upsize your database to SharePoint Server (as described in Chapter 21).