Microsoft Access Self Join

Articles Index

Articles Index

Course Listing

Free Catalog

Request Information

What's New

View related PowerPoint slides on Slideshare

Glossary of Database Terms

 

Page 1 of 1

About

Background

A self join joins a table to itself. This is useful for hierarchical relationships such as parts lists, employee to supervisor reporting, customer referrals, etc. These are all recursive relationships.

The purpose of this article is show how to create a Microsoft Access query based on these recursive relationships. We will consider the one-to-many case, the most common, in this article.

You can download the Microsoft Access sample database, SelfJoin.mdb, at box.net. A downloadable PowerPoint presentation version of this article can be found on Slideshare.

Business Scenario

We want to construct a query that will list all employees and their supervisor, if they have one. The employees and their supervisors are in a single employees table. After all, the supervisors are also employees.

Database Design

Relationship Designer

Below is a screenshot of the Microsoft (MS) Access relationship design of our sample SelfJoin database. It is the same as the Factory 2000 sample database design. Note the MgrNo foreign key. It actually references the EmpNo in the same table.

Factory 2000 Database Design

Employees Table

Below is a screenshot of the sample data in the employees table. The MgrNo field references the EmpNo field in the same table.

Employees Table Data

Betty reports to Alicia who in turn reports to Mary. In this example Mary does not have a direct supervisor. This example is similar to many recursive relationships in which the top of the tree is denoted by a null foreign key.

Designing the self join query

Step 1

Our self join query will be a normal query, joining two tables. The difference is that this time the two tables are really the same table. Just add the employees table twice to the query designer. The second time the table is added a _1 suffix is appended. So far as MS Access is concerned we will be joining two different tables named employees and employees_1.

Query designer step 1

Assign an Alias

The next step is to assign an alias to the second employees table above. Right click on the table name in the MS Access query designer and assign an alias as shown below. Call it managers. This step is not strictly necessary but your query will be more meaningful if you do it. What this does is give the second table what is called a table alias which is another name for the table. All references to the table must then use this alias. Table aliases are often used in SQL to give a table a short name that a query can use. They are not so common in MS Access but are, nevertheless, helpful here.

Assign Table Alias

Create Table Relationships

Create the relationship between the two tables. In this case we want to make the relationship an outer join. This means that all employees will be listed even if they have no supervisor. If the MgrNo foreign key has a null value then a normal inner join would exclude that employee.

Finish by adding the fields you want to show to the query designer QBE grid. Note how the table names in the QBE grid reflect the new alias we created. I added a calculated field, Relationship, which will contain the value "Reports to". This is just an added touch to enhance the display. It is not necessary for the self join.

Outer Join

Save the query and run it.

Query results

Below are the results of running the query with the sample data. All three employees are listed along with their manager.

Query results


Conclusion

This article showed how to create a self join in Microsoft Access using a table alias. Hopefully, you will find these self joins a useful tool in creating Microsoft Access database applications. I am sure you will find many uses for them.

Submit the form below to be notified of new articles or other Microsoft Access resources made available...

Receive course announcements and news. Sign Up Today!





Email Marketing by VerticalResponse

Learn more about our Microsoft Access courses..

View MS Access course catalog.

 


Please send comments and suggestions to WebMaster at dhdurso.org

Copyright(2001-2013) D.H.D'Urso & Associates
Laguna Niguel, Orange County, California

Index | Next | Top