Course summary: Create table relationships (Access basics, part 2)

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 Relationshipsto set referential integrity, and cascading updates/deletes as needed.


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.


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.

See also

Need more help?

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?