Access Data Analysis Cookbook (Cookbooks)

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

1.13.2 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 table will automatically have a "_1" appended to the end of the table name to differentiate it from the first one. Now join the two tables together by dragging the SupervisorID field from the first copy of the table (the one without the _1 suffix) to the EmployeeID field in the second copy of the table (the one with the _1 suffix).

4. Drag any fields you wish to include in the query to the query grid. The fields from the first copy of the table describe the employee; the fields from the second copy of the table describe the supervisor. Because the fields of the two tables have the same names remember they're really two copies of the same table you need to alias (rename) any fields from the second table to avoid confusion. For example, in the qryEmployeeSupervisors1 query, we included the following calculated field, named Supervisor, which displays the name of the employee's immediate supervisor:

   Supervisor: [tblEmployees_1].[FirstName] & " " & [tblEmployees_1].[LastName]

   Notice that the fields that make up the supervisor name both come from the second copy of the employee table.

5. If you run the query at this point, you will get only employees with supervisors (see Figure 1-34). That's because this version of the query named qryEmployeeSupervisors in the sample database uses an inner join. To see all employees, even those without a supervisor (in our example this would include Shannon Dodd, the company's president), you must change the type of join between the two tables to a left outer join. Double-click on the join line you created in Step 3. At the Join Properties dialog, select choice #2 (see Figure 1-35).

6. Run the query, and the datasheet will display the employee-supervisor relationship.

Now, open tblEmployees in 01-13.MDB. This table, which is shown in Figure 1-36, contains a primary key, EmployeeID, and the usual name and address fields. In addition, it contains a field, SupervisorID, which stores the EmployeeID of the employee's immediate supervisor. Now run the query qryEmployeeSupervisors1. This query uses a self-join to display a recursive relationship between employee and supervisor; its datasheet lists each employee and his or her immediate supervisor (see Figure 1-37).

1.13.3 Discussion

You can always model an employee-supervisor relationship as two tables in the database. Put all supervised employees in one table and supervisors in a second table. Then create a regular select query to list out all employees and their supervisors. This design, however, forces you to duplicate the structure of the employee table. It also means that you must pull data from two tables to create a list of all employees in the company. Finally, this design makes it difficult to model a situation in which employee A supervises employee B, who supervises employee C.

A better solution is to store both the descriptive employee information and the information that defines the employee-supervisor hierarchy in one table. You can view the employee-supervisor relationship using a self-join query. You can create a self-join query by adding a table to the query twice and joining a field in the first copy of the table to a different field in the second copy of the table. The key to a self-join query lies in first having a table that is designed to store the information for the recursive relationship.

The sample query qryEmployeeSupervisors1 displays the employee-supervisor relationship to one level. That is, it shows each employee and his or her immediate supervisor. But you aren't limited to displaying one level of the hierarchy the sample query qryEmployeeSupervisors3 displays three levels of the employee-supervisor relationship using four copies of tblEmployees and three left outer joins. The design of qryEmployeeSupervisors3 is shown in Figure 1-38; the output is shown in Figure 1-39.

You can use the Access Relationships dialog to enforce referential integrity for recursive relationships. Select Tools Relationships to display the Relationships dialog and add two copies of the table with the recursive relationship. Join the two copies of the table together as if you were creating a self-join query. Choose to establish referential integrity, optionally checking the cascading updates and deletes checkboxes. Click on Create to create the new relationship. Now when you enter a value for SupervisorID, Access will prevent you from entering any reference to an EmployeeID that doesn't already exist.

Although the sample database uses an employee-supervisor relationship example, you can use the techniques discussed in this solution to model other types of hierarchical relationships. This will work, however, only if each "child" record has only one "parent." In this example, each employee has only one supervisor. For hierarchies in which one child can have many parents such as parts and assemblies in a bill of materials database a separate table is needed to contain the multiple records needed for each child, each one specifying a different parent.