How to Create Database Relationship using Access
So you’ve made the move from a spreadsheet to a database. You’ve set up your tables and painstakingly transferred all of your precious data. You take a well-deserved break, sit back and look at the tables you’ve created. Wait a second — they look strangely familiar to the spreadsheets you’ve just disowned. Did you just reinvent the wheel? What’s the difference between a spreadsheet and a database anyway?
One of the major advantages of databases such as Microsoft Access is their ability to maintain relationships between different data tables. The power of a database makes it possible to correlate data in many ways and ensure the consistency (or referential integrity) of this data from table to table. In this article we’ll take a look at the process of creating a simple relationship using a Microsoft Access database.
Imagine a small database we’ve created for the Acme Widget Company. We want to track both our employees and our customer orders. We might use a table structure similar to the one shown below:
Notice that each order is associated with a specific employee. This information overlap presents the perfect situation for the use of a database relationship. Together we’ll create a Foriegn Key relationship that instructs the database that the EmployeeID column in the Orders table corresponds to the EmployeeID column in the Employees table.
Once the relationship is established, we’ve unleashed a powerful set of features in Microsoft Access. The database will ensure that only values corresponding to a valid employee (as listed in the Employees table) can be inserted in the Orders table. Additionally, we have the option of instructing the database to remove all orders associated with an employee when the employee is deleted from the Employees table.
Now let’s dig in and create that relationship!
1. Open the Relationships Window from the Tools menu.
2. Add the appropriate tables. We’ll add the Employees and Orders tables — you can use the Shift-Click combination to select both at the same time. Once you’ve highlighted the tables click the Add button.
3. Open the Edit Relationships tool. You’ll find it on the Relationships pull-down menu.
4. Create a new relationship.
5. Fill in the appropriate details. The left table is the primary source of the data and the right table is the desired location of the foreign key. In this case, our original information is stored in the Employees table and the foreign key will be in the Orders table. The field name is EmployeeID in both cases. Once you’ve selected the appropriate items, click OK to continue.
6. Choose whether to enforce Referential Integrity. In most circumstances, you will want to select this option. This is the real power of a relationship — it ensures that new records in the Orders table only contain the IDs of valid employees from the Employees table.
You’ll also notice two other options here. The “Cascade Update Related Fields” option ensures that if an EmployeeID changes in the Employees table that change is propagated to all related records in the Orders table. Similarly, the “Cascade Delete Related Records” option removes all related Orders records when an Employee record is removed. The use of these options will depend upon the particular requirements of your database. In this example, we’ll won’t utilize either one.
7. Open the Join Type dialog box.
8. Select a join type. The three options are shown in the figure below. If you’re familiar with SQL, you might notice that the first option corresponds to an inner join, the second to a left outer join and the final to a right outer join. We’ll use an inner join for our example.
9. Create the relationship.
Voila! The relationship window now reflects our new relationship!