Microsoft Access Table Relationships
An ideal database is free of redundant or duplicate data. To achieve that, you must split your data into many subject-based tables so each subject is presented only once. To do that in Microsoft Access, you place common fields in tables that are related. But before you do that, you must need to understand the relationships between your tables, then you can specify these relationships in your Access 2013 database.
In this article, I will elaborate to you the ten things you need to know about table relationships in Microsoft Access.
1. A one-to-many relationship is when one record in the parent table connects to many records in the child table.
To exemplify this, let’s take for example two tables: one for doctors and the other is for patients. A doctor can have several patients. In this case, you will not need to repeat all the doctor information on each patient.
2. A one-to-one relationship connects one record in the parent table to one record in the child table.
This type of table relationship is not common but can sometimes be used if you want to split a single table that contains many fields into two. For example, the customers table can have first name, last name, address, city, state, work email, mobile number, home phone number, and work phone number. Instead of joining all of these fields into one table, you can create another table and put all the contact information fields into that table.
3. Relate the primary key field in the parent table with that same field (not the primary key) in the child table (considered as the foreign or secondary key).
This is the most common case. The parent table contains a primary key field and the child table will also have the same field name. For instance, the customers table and the orders table might share a Customer_ID. Customer_ID is a field name that is normally the primary key of the customers table and a foreign key in the table for orders.
4. Fields connected in the relationship must be of similar data type.
So if in the parent table you have a text field, you should relate that to a text field in the child table. You cannot relate a number field from a parent table to a text field in the child table or vice versa.
Microsoft Access Table Relationships
5. In the Edit Relationships dialogue box, tick on the check box where it says “Enforce Referential Integrity” to prevent “orphan” record entry in the child table.
An orphan is a record contained in the child table that doesn’t have a matching record in the parent table. The typical example is an order in an Orders table for a customer not in the Customers table. The purpose of setting the referential integrity is avoid these kinds of orphan orders.
6. Still in the Edit Relationship dialogue box, set the Cascade Update Related Fields to update the key value in the child table when it’s updated in the parent table.
The Cascade Update Related Fields function simplifies editing a mistyped or misspelled field by updating that field in the child table whenever a related field is edited in the parent table.
Suppose there are two tables: Expense_Type table and Expenses table. The Expense_Type table contains a list of categories (field name Category) while the Expenses table has a field called Category. The Category field in the Expenses table is populated by a combo box that gets its data from the Expense_Type table’s Category field.
And assume that a typo was made for Dining – it was spelled Dinning. If you have set the Cascade Update Related Fields and you edited Dinning in the ExpenseType table, the effect will also change every related record in the Expenses table.
7. 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.
When you set Cascade Delete Related Records in a relationship between two tables (a Customer table and an Order table connected by Customer_ID) and you are going to delete a customer record, all the orders for that customer will also be deleted.
8. Setting relationships between tables automatically sets joins for those tables when creating queries.
If you set a relationship between Customers table and Orders table using Customer_ID, you will see that join line when you add those tables to a new query in query design view.
9. Deleting a field that is part of a relationship will also delete the relationship.
A relationship will not exist when one of the two fields is missing. In Access, a broken relationship is deleted when you delete a field that is part of that relationship.
10. If a primary key is part of a relationship, it is impossible to change the primary key in that table to another field without first deleting the relationship.
When you want to change primary keys in a table where its primary key is part of a relationship, you need to first open the Relationships window and delete the relationship. MS Access will let you change the primary key to another field in that table after you delete the relationship.
To learn more about table relationships in Microsoft Access, consider one of my eBooks on understanding Access database relationships which comes with a 30 day money back guarantee if not satisfied.