print friendly version

Questions and answers

What is the One to Many (One-to-Many) Relationship in Microsoft Access?

A one-to-many relationship, often referred to as a "master-detail" or "parent-child" relationship, is the most usual relationship between two tables in a database.

Common scenarios include customer/purchase data, patient/medical-record data, and student/course-result data. For example, each customer is associated with at least one order record. Valued customers have many order records involving significant sums and often a user needs to view one in connection with the other. In a one-to-many relationship, a record in Table A can have (none or one or) more than one matching record in Table B, but for every record in Table B there is exactly one record in Table A.

When creating a relationship between two tables MS Access provides us with the Referential Integrity feature. This feature prevents adding records to a detail table for which there is no matching record in the master table. It will also cause the key fields in the detail table to be changed when the corresponding key fields in the master are changed - this is commonly referred to as a cascading update. The second option is to enable cascading deletes. This causes the deletion of all the related records in a detail table when the corresponding record in the master table gets deleted.

Help us to improve this answer

Please suggest an improvement
(login needed, link opens in new window)

Your views are welcome and will help other readers of this page.


This is question number 899, which appears in the following categories:

Created by Jason Bailey on 22 November 2001 and last updated by Adrian Chorlton on 1 August 2016