Access Cookbook

Use a Query to Show the Relationship Between Employees and Supervisors

Problem

You have a table that includes information on every employee in the company, including management. You’d like to be able to store information on who supervises each employee in this same table and then be able to create a query to show this hierarchical relationship.

Solution

You can display an employee-supervisor hierarchical relationship, also known as a recursive relationship, in Access with a select query that uses a self-join to join another copy of a table to itself. This solution shows how to create the table that will store the necessary recursive information and then how to create the self-join query to list each employee and his or her supervisor.

To create the employee table and a query that displays the recursive employee-supervisor relationship, follow these steps:

1. Create the employee table. This table should contain both an EmployeeID field and a SupervisorID field. These fields must have the same field size. In the sample database, tblEmployees contains the EmployeeID and SupervisorID fields. Because EmployeeID is an AutoNumber field with the FieldSize property set to Long Integer, SupervisorID must be a Number field with a FieldSize of Long Integer.

2. Enter data into the employee table, making sure that the SupervisorID field is equal to the EmployeeID field of that employee’s immediate supervisor.

3. Create a new select query. Add two copies of the employee table. The second copy of the ...