databasedev.co.uk - database solutions and downloads for microsoft access

Creating a Self-Join Query to relate data within a table in Microsoft Access:

When working with Microsoft Access Queries you will no doubt work with a range of join types, from the default Inner Join to the more complicated Outer Joins. In some cases, however, the related data is all within a single table. In this situation you will use a special join type that can be used to retrieve the desired data.

This type of relationship does not exist between multiple database tables, it is actually a relationship that exists between the records within a single database table.

Consider the scenario where all of the data you require is contained within a single table, but you may need to be able to relate columns within that table to each other to extract the desired data. Usual examples of this type of data relate to Employee information, where the table may have both an Employee's ID number for each record and also a field that displays the ID number of an Employee's supervisor or manager.

To retrieve the data you need to be able to relate the data to itself. For this purpose the Self-Join is designed to do exactly this action.

Self-Joins

The Self-Join is a join that relates data in a table to itself. A table is joined to itself based upon a field or combination of fields that have duplicate data in different records. The data-type of the inter-related columns must be of the same type.

You will be able to see in the example below an example of a single table that contains related data. The lngID field and the lngSupervisorID fields are of the same data type and contain the same data across different records:

How do we relate Date within a single Table?

To Create a Self-Join

• Open a query in Design View
• Click on the Show Table button
• Add the table to your query so that it appears twice
• Join the related fields
• Complete the query design by adding the fields that you want to display in the grid

Using an Alias in the Query

When adding the same database table to the query design grid multiple times, it is a good idea if you change the name of one of the tables. When you assign an alias to a table in the query design grid, you are not renaming the underlying table.

Creating a Self Join Query

If you consider the above example, where we would need to design a query to display the Supervisors names for each Employee in our database. Our table contains information about Employees and their Supervisors, with the Supervisor being identified by his/her EmployeeID number.

In order for us to design a query that will display the Supervisor name for each Employee held in the database table we will need to relate the lngEmpID field and the lngSupervisorID field.

In a new query design grid we will add the tblSupervisors table twice as shown below:

You will see that this assigns the same table name again, followed by an underscore and number. We will assign an alias to the second table by right-clicking on the second table and choosing to display the properties menu.

In the Alias property box, type in the new name that you will alias the table with and close the property dialog as shown below:

We can now create the self-join based upon the lngSupervisorID from the Supervisors table and the lngEmpID from the newly named Managers table by dragging one field to the other:

We can now add the required fields to the query design grid. In this example, we are going to add the strFirstName and the strLastName fields from the tblSupervisors field list and the strLastName from the Managers field list.

We should now change the caption of the strLastName field that will display the data from the Managers table as at present we have two strLastName fields. In the design grid, we shall right-click on the strLastName field from the Managers table and choose Properties. This will display the field properties dialog box where we can set a new caption:

We should repeat this process to rename the strLastName field of tblSupervisors to show just LastName.

Now when we run the query we will be able to see the Employee's First and Last Names and also the Last Name (ManagerName) of their manager. An example of the results is shown below: