Access 2010: The Missing Manual

More Exotic Relationships

As you’ve learned, 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.

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.

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 is important only to the people in the engineering department, so you may choose to split it into a separate table (named something like ProductsEngineering). 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 relationship—by dragging the fields in the Relationships tab (Figure 5-15). The only difference is that the linked fields in ...