Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 1.13 Use a Query to Show the Relationship Between Employees and Supervisors

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
   namesremember they're really two copies of
   the same tableyou 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 querynamed qryEmployeeSupervisors in the sample
   databaseuses 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

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

Figure 1-36. The SupervisorID field stores information on each employee's supervisor

Figure 1-37. Output of qryEmployeeSupervisors1

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

Figure 1-38. qryEmployeeSupervisors3 shows three levels of the employee-supervisor relationship

Figure 1-39. Output of qryEmployeeSupervisors3

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 parentssuch as parts and
assemblies in a bill of materials databasea separate table is
needed to contain the multiple records needed for each child, each
one specifying a different parent.