Access Cookbook - Ken Getz [29]
Enter data into the employee table, making sure that the SupervisorID field is equal to the EmployeeID field of that employee's immediate supervisor.
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).
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.
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).
Figure 1-34. This self-join query uses an inner join
Figure 1-35. The Join Properties dialog allows you to create left or right outer joins
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).
Figure 1-36. The SupervisorID field stores information on each employee's supervisor
Figure 1-37. Output of qryEmployeeSupervisors1
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