Advanced Database – Apply a self join

For this example, we need to create a new table with the properties and data shown.

65 Apply Join 1
66 Apply Join 2
67 Apply Self Join

A self join links a table to itself.

In the employee table for instance, if we want a query to list each employee and the name of who they report to, we would need a self join.

Let’s first try without a self join. Set up a new query, adding the Employees table twice. Notice it automatically names the second table ‘Employees_1’.

Select the fields shown to the left and press F5 to run the query.

68 Self Join Report

There are a couple of problems with this query. First of all it repeats each staff member several times. Also it displays the staff number, not name of the person they report to.

We can solve this by setting up a self-join.

Press F4 to remove the table.

69 self join with Inner Join

To apply a self join, decide which fields we want to connect with an inner join.

In this case we want the staff no given in the ‘Reports to’ field of the primary table to relate to the ‘Staff No’ field of the duplicate table.

Therefore, drag a join from ‘Reports to’ to ‘Staff No’ as shown.

Notice we also change the third displayed field to ‘Name’ from ‘Employees_1’. Press F5.

70 Self Join with Inner Report

This time it displays the query table exactly the way we want, with the name of the manager instead of the staff no, and no repeated records.

To modify or delete the join, simply right-click the connecting line and select Edit or Delete.

Press F4 to close the query results.