Microsoft Access 2010 Bible

Part I: Access Building Blocks

168

FIGURE 4.30

A query with a self join involving the Employees table

Notice in Figure 4.30 that a right outer join has been established between the Field Lists in the

tables pane. This join means that all records in Employees are returned by the query, even if they

are not joined to a record in Employees_1. In the Northwind Employees table, supervisors do

not report to anyone, so they will have no matching records in Employee_1. The outer join is

necessary to see supervisors in the query’s results.

The recordset returned by qrySelfJoin is shown in Figure 4.31. Several employees (like Laura

Callahan) report to Andrew Fuller, while others report to Steven Buchanan. Steven Buchanan, in

turn, reports to Andrew Fuller. Andrew Fuller is the only employee who has no supervisor.

FIGURE 4.31

All Northwind employees and their supervisors