Microsoft Access 2010 Bible

Chapter 4: Selecting Data with Queries

167

FIGURE 4.29

A datasheet with a right outer join. It shows all customers, including those with no sales.

All records returned from this table

Outer join line This table may be missing records

Working with self-joins

A self-join is a very special type of join that is quite easy to understand, particularly when given a

simple example. A self-join occurs when a field in a table is related to another, or the same, field in

the table. The classic example of a self-join is a table containing employee records. Very often this

table will include a field specifying an individual’s supervisor. But, because supervisors are also

employees, the supervisor records are contained in the same table.

The Collectible Mini Cars database does not include any examples of self-joined tables, so I have to

borrow the Employees table from the Northwind Traders example database. The Northwind

Employees table uses EmployeeID as the primary key, and includes a field named ReportsTo

that specifies each employee’s supervisor.

Creating a self-join query is easy. In this case, add the Northwind Employees table twice to a new

query. The first time it is added, Access uses the default heading (Employees) for the Field List.

The second time the table is added Access uses Employees_1 as the Field List heading. No join

line is added to the query design because Access has no idea why you’ve added the same table

twice. You must explicitly establish a join line by dragging the ReportsTo field from Employees and

dropping it on the EmployeeID field in Employees_1 (see Figure 4.30).