10 Things You Need to Know about Relationships in Access 2013
A one-to-many relationship connects one record in the parent table to many records in the child table.
Set the relationship to connect orders to customers or doctors to patients. One customer can have many orders and one doctor can have many patients. This way, you don’t have to repeat all the customer contact information on each order nor all the doctor contact information on each patient.
A one-to-one relationship connects one record in the parent table to one record in the child table.
This isn’t a common relationship type but can be used if you need to split a table that contains many fields into two tables.
Relate the primary key field in the parent table with that same field (not the primary key) in the child table.
This is the most common scenario. The parent table contains a primary key field and the child table contains the same field name. For example, a Customers and Orders table might share a CustomerID. CustomerID is normally the primary key of Customers and a foreign key in Orders.
Fields joined in the relationship must be of the same data type.
You can’t relate a text field in the parent table to a number field in the child table or visa-versa. The fields must be either text fields or number fields in each table.
Enforce referential integrity in the Edit Relationships dialog to prevent orphan record entry in the child table.
An orphan is a record in the child table that doesn’t have a corresponding record in the parent table. The classic example is an order in an Orders table for a customer not in the Customers table. Referential integrity prevents entering these kinds of orphan orders.
Set cascade update related fields in the Edit Relationships dialog to update the key value in the child table when it’s updated in the parent table.
Suppose you have two tables, a list of categories in an ExpenseType table (field name Category) and a field in an Expenses table called Category. The Category field in Expenses is populated by a combo box that pulls its data from the ExpenseType table’s Category field.
Further suppose that a typo was made for Dining, it was spelled Dinning. If you’d set cascade update related fields, you can edit Dinning in ExpenseType to Dining and it will change on every record where used in the Expenses table.
Set cascade delete related records in the Edit Relationships dialog to delete related records in the child table when the corresponding record is deleted in the parent table.
With this setting on in a relationship between CustomerID in Customer and CustomerID in Orders, delete a customer record and you’ll delete all the orders for that customer.
Setting relationships between tables automatically sets joins for those tables when building queries.
Relationship joins are carried through to query design view. If you set a relationship between Customers and Orders on CustomerID, you’ll see that join line when you add those tables to a new query in query design.
Delete a field that is part of a relationship and you’ll delete the relationship.
You can’t have a relationship between two fields if one of them is missing, right? So, Access removes the broken relationship when you delete a field that’s part of that relationship.
If a primary key is part of a relationship, you can’t change the primary key in that table to another field without first deleting the relationship.
To change primary keys in a table where its primary key is part of a relationship, you must first open the Relationships window and delete the relationship before Access will allow you to change the primary key to another field in that table.