A self join enables you to join a table to itself. This is often done so that information in a single table can appear to exist in two separate tables. A classic example is seen with employees and supervisors. Two fields are included in the Employees table; one field includes the EmployeeID of the employee being described in the record, and the other field specifies the EmployeeID of the employee's supervisor. If you want to see a list of employee names and the names of their supervisors, you'll need to use a self join.To build a self-join query, follow these steps:
Click the Queries tab of the Database window and then click New.
Select Design view and click OK.
From the Show Tables dialog box, add the table to be used in the self join to the query grid two times. Click Close. Notice that the second instance of the table appears with an underscore and the number 1.
To change the alias of the second table, right-click on top of the table in the query grid and select Properties. Change the Alias property as desired. In Figure 11.31, the alias has been changed to Supervisors.
Figure 11.31. Building a self join.
To establish a join between the table and its alias, click and drag from the field in one table that corresponds to the field in the aliased table. In Figure 11.32, the SupervisorID field of the tblEmployees table has been joined with the EmployeeID field from the aliased table.
Figure 11.32. Establishing a self join between the table and its alias.
Drag the appropriate fields to the query grid. In Figure 11.32, the FirstName and LastName fields are included from the tblEmployees table. The SupervisorName expression (a concatenation of the supervisor's first and last names) is supplied from the copy of the table with the Supervisors alias.
Chapter 3, "Relationships: Your Key to Data Integrity."
لطفا منتظر باشید ...
