• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint


A Self-Join allows you to join a table to itself, which 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 employees and 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 12.31, the alias has been changed to Supervisors.

Figure 12.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 12.32, the ReportsTo field of the tblEmpInfo table has been joined with the EmployeeID field from the aliased table.

Figure 12.32. Establishing a Self-Join between the table and its alias.

Drag the appropriate fields to the query grid. In Figure 12.32, the FirstName and LastName fields are included from the tblEmpInfo 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.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint