Database Relationships in Microsoft Access 2013

Database relationships in Microsoft Access 2013

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 that contains one table for employees with the following fields:

  • Employee ID (primary key)
  • First Name
  • Last Name
  • Extension
  • Salary
  • Manager

We then might have a second table containing the orders taken by our employees. That orders table might contain the following fields:

  • Order ID (primary key)
  • Customer ID
  • Employee ID
  • Item ID
  • Quantity

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 Foreign 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.

Here's how we go about creating the relationship in Access 2013:

  1. From the Database Tools tab on the Ribbon, click Relationships.
  2. Highlight the first table that you want to make part of the relationship (Employees) and click Add.
  3. Repeat step 2 for the second table (Orders).
  4. Click Close. You should now see the two tables in the Relationships window.
  5. Click Edit Relationships in the ribbon.
  6. Click Create New.
  7. In the Create New window, choose Employees as the Left Table Name and Orders as the Right Table Name.
  8. Choose EmployeeID as both the Left Column Name and Right Column Name.
  9. Click OK to close the Create New window.
  10. Use the checkbox in the Edit Relationships window to 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.
  11. You'll also notice two other options here. The Cascade Update Related Fields option ensures that if an EmployeeID change 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.
  1. Click Join Type to see the three options available to you. 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.
  2. Include ALL records from 'Employees' and only those records from 'Orders' where the joined fields are equal.
  3. Include ALL records from 'Orders' and only those records from 'Employees' where the joined fields are equal.
  4. Click OK to close the Join Properties window.
  5. Click Create to close the Edit Relationships window.
  6. You should now see a diagram showing the relationship between the two tables.