|[ Team LiB ]|
Recipe 1.13 Use a Query to Show the Relationship Between Employees and Supervisors
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.
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:
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).
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.
|[ Team LiB ]|