Relationships between fields, tables, records, and so on, are the most common things you deal with in Access 2016. Keep these ten tips in mind:
-
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 locations to events. One customer can have many orders, and one location can have many events. This way, you don't have to repeat all the customer contact information on each order, nor all the location information on each event.
-
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 (called the foreign 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 vice-versa. The fields must be either text fields or number fields in each table.
-
Enforce referential integrity in the Edit Relationships dialog box 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 box 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 box 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 LocationID in Locations and LocationID in Events, delete a location record and you'll delete all the events for that location.
-
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 Locations and Events on LocationID, 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. In the Relationships window, right-click on the join line between the two tables and choose Delete to delete the relationship.
