For this example, we need to create a new table with the properties and data shown.
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.
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.
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.
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.