Why relationships
They link your tables, so they’re a critical part of your database, and they inform your queries.
Relationship types
One-to-one, one-to-many, and many-to-many. One-to-many is most common, many-to-many are part of most transaction and student databases, 1:1 is rare.
Create a one-to-many
In datasheet view, select your foreign key or a blank field.
- Foreign key: Select the field, then Fields > More Fields > Lookup & Relationship, and follow the wizard.
- Blank field: Select the header, then Lookup & Relationship and follow the wizard.
Use the Relationships pane
Child table must have a foreign key. Select Database Tools > Relationships, then drag the primary key from the parent table to the foreign key in the child table. Use Edit Relationships to set referential integrity, and cascading updates/deletes as needed.
Many-to-many
Create > Table Design, create a junction table. In that table, duplicate the primary keys from the tables on both sides of the relationship, select both fields and set them as a composite key.
One-to-one
Create a one-to-many relationship, then:
- Design view: Set the Indexed property for both keys to “No,” or “Yes, No Duplicates.”
- Ribbon, set both keys to Indexed and Unique.