Relate a Table to Itself to Maximize Your Data's Value
This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.ACCESS 97, 2000
Relate a Table to Itself to Maximize Your Data's Value
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.by Susan Sales Harkins
Last month we showed you how to use the Caption property to seemingly change a field's name, without having to update dependent references. Tables have a similar propertythe Alias property. You won't find this among the table's regular properties, as you'll use it in queries when you need to include more than one copy of a table in the same query. When this happens, the second table is much easier to work with if you give it a descriptive name, or alias, to distinguish it from its original name.
You might be wondering why you'd want to include two or more copies of the same table in a query. In this article, we'll explain the why and how of relating a table to itself in a query. We'll also show you how setting a table's Alias property can greatly improve the readability of your query.
The query technique we'll examine is what is known as a self-join. Technically, a self-join combines records from the same tablewith a self-join, you're simply relating a table to itself. You'll use a self-join when you want to find records that have values in common with other rows in the same table. Unlike the other joins you're used to, which link two distinct tables, a self-join adds a second instance of the same table. By doing so, you can compare values from a column in the first instance of the table to values from a column in the second instance.
By design, Access allows you to include more than one instance of the same table in a query. When you add the second instance, Access automatically tags on a prefix to the table's name, which identifies the table accordingly. For instance, if you add a second instance of a table named Employees, Access names that second instance Employees_1.
You can work with the default table name that Access assigns, but you might find it easier to give the table a new name. That's where the Alias property comes into play. When you're working in the query design grid, an alias may not seem important. However, once you're working with SQL statements, you'll find the capability more helpful.
When to use a self-join
At this point, we've defined what a self-join is. Now, let's examine why you'd actually want to use one. Perhaps the easiest way to get a clear picture of how to effectively employ one is to take a look at a simple example. The Northwind database that comes with Access provides a textbook example for using a self-join.
Specifically, the Employees table uses an AutoNumber field to assign a unique identifying value for each employee. Instead of creating a second table of managers, the Employees table's records use the appropriate EmployeeID value to identify each employee's manager. This arrangement negates the necessity for a second table, but the results can sometimes be a bit awkward to work with. We show the employee names and the appropriate reports to values from the Employees table in Figure A.
Northwind eliminates the need for a separate table by recording the appropriate EmployeeID value in the ReportsTo field.
Creating an alias
Now, let's suppose you want to list the names of all of the people in the Employees table, along with their managers' names. A self-join is the solution. To create this query, open the Northwind sample database. Then, switch to the Queries sheet of the Database window and click the New button. When the New Query dialog box appears, click OK.
Access now presents the query design grid and the open Show Table dialog box. Select Employees in the dialog box and click Add. Then, click Add againAccess adds a second instance of the table and assigns a default alias of Employees_1, as shown in Figure B.
Access creates a default alias by adding a number to the original table name.
To make the purpose of the second instance of the Employees table more meaningful, let's change its alias. First, click the Close button to dismiss the Show Table dialog box. Then, right-click on the Employee_1 field list and choose Properties from the resulting shortcut menu. Change the value in the Alias text box to Supervisors. Finally, click the Close button on the Properties dialog box.
It's not necessary to specify an alias for a second, or even a third, instance of a tablethe defaults will work just fine. However, doing so makes working with the two instances less confusing, especially once you add a relationship. In the case of our example query, we'll relate the Employees.[ReportsTo] field to the Supervisors.[EmployeeID] field.
To set up the relationship, drag ReportsTo from the Employees field list to EmployeeID in the Supervisors field list. As a result, our query will display a record for each record where both values are the same. In other words, when the value in the Supervisors.[EmployeeID] field matches a value in the Employees.[ReportsTo] field, the query will return a record. When the ReportsTo field in Employees is empty, the query won't return a record for that employee (since the EmployeeID field is an AutoNumber field, it will never be blank.).
Using Figure C for reference, set up the query in the design grid. The expression
Manager: [Supervisors].[FirstName] & " " & [Supervisors].[LastName]
concatenates the FirstName and LastName fields with a space in between. To see the complete results, run the query by clicking the Run button on the Query Design toolbar. We've shown those results in Figure D. The first record lists Nancy Davolio as the employee. If you review the Employees table shown in Figure A, you'll see that her ReportsTo field contains the value 2 and the EmployeeID value 2 belongs to Andrew Fuller. Our query correctly lists Andrew Fuller as Nancy Davolio's manager.
We gave the second instance of Employees a more meaningful aliasSupervisors.
The Manager expression returns the manager names for the corresponding employees.
Returning all the records
You may have noticed that Andrew Fuller's record, which appears in Figure A, isn't included in the query results. That's because his ReportsTo field is blank, which we discussed a bit earlier. Sometimes this is what you'll want. When it isn't, you can easily return all the employee records, even if the ReportsTo field is blank.
First, select SQL View from the View button's menu to examine the SQL statement behind this query. We've reformatted the statement, but it basically reads
As you can see, this query uses an INNER JOIN to determine which records to include in the results. An INNER JOIN will return only those records where the values are the same in the related field. To return all the employee records, we'll need a LEFT JOIN.
SELECT Employees.LastName, Employees.FirstName, [Supervisors].[FirstName] & " " & [Supervisors].[LastName] AS Manager FROM Employees INNER JOIN Employees AS Supervisors ON Employees.ReportsTo = Supervisors.EmployeeID;
There are two ways to modify our query. You can replace the INNER keyword with the keyword LEFT. Or, you can switch to Design view, and edit the relationship line between the two tables in the query design grid. To make the change in Design view, right-click on the relationship line and choose Join Properties from the shortcut menu. Then, select the second option in the Join Properties dialog box, as shown in Figure E. The results of the changed query are shown in Figure F. As you can see, the LEFT JOIN includes all the employee records, including those with no manager identified in the ReportsTo field.
The second option creates a LEFT JOIN relationship between Employees and Supervisors.
A LEFT JOIN returns all Employees records regardless of whether there are matching Supervisors records.
When working with a SQL statement, an alias is invaluable in a self-join query, because the alias helps identify the different sides of the join. In this case, Employees is the left side and Supervisors is the right. If you were working with an Employees and Employees_1 table, the tables might not be as easy to distinguish.
Self-joins may not be common, but they're certainly useful in the right circumstances. In this article, we used a self-join to return managers for each employee in the Northwind database's Employees table. When working with self-joins, you'll probably want to take advantage of the Alias property to clearly identify each instance of the table you've joined to itself.
Copyright © 2000 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.