Microsoft Office Tutorials and References
In Depth Information
One-to-many relationship and sample query
fields from Position table
fields from Employer table
A one-to-many relationship exists between two tables when one record in the first table
matches zero, one, or many records in the second table, and when one record in the sec-
ond table matches exactly one record in the first table. For example, as shown in Figure 3-5,
employers 10126 and 10191 each have two available positions, and employers 10122,
10125, 10190, and 10198 each have one available position. Every position has a single
Access refers to the two tables that form a relationship as the primary table and the related
table. The primary table is the “one” table in a one-to-many relationship; in Figure 3-5, the
Employer table is the primary table because there is only one employer for each available
position. The related table is the “many” table; in Figure 3-5, the Position table is the
related table because there can be many positions offered by each employer.
Because related data is stored in two tables, inconsistencies between the tables can
occur. Consider the following scenarios:
• Matt adds a position record to the Position table for a new employer, Glen Cove Inn,
using EmployerID 10132. Matt did not first add the new employer’s information to the
Employer table, so this position does not have a matching record in the Employer table.
The data is inconsistent, and the position record is considered to be an orphaned record .
• Matt changes the EmployerID in the Employer table for BaySide Inn & Country Club from
10126 to 10128. Two orphaned records for employer 10126 now exist in the Position
table, and the database is inconsistent.