- In query Design view, drag a field from the field list for one table or query to the equivalent field (a field of the same or compatible data type containing similar data) in the field list for the other table or query.
With this type of join, Microsoft Access selects records from both tables or queries only when the values in the joined fields are equal.
Note Join numeric fields only if the FieldSize property settings for both are Byte, Integer, or Long Integer.
In some cases, you want to join two copies of the same table or query, called a self-join, that combines records from the same table when there are matching values in the joined fields. For example, say you have an Employees table in which the ReportsTo field for each employee's record displays his or her manager's ID instead of name. You could use a self-join to display the manager's name in each employee's record instead.
Join two copies of the same table or query
- In query Design view, add the table to the query twice.
- Create the join by dragging a field in the first table's field list to the field you want to relate it to in the second field list.
Note Microsoft Access appends "_1" to the table name in the second field list. For example, if you add the Employees table twice, the first field list is titled "Employees," and the second field list is titled "Employees_1." You can rename the table to something more descriptive by setting the Alias or Caption property for the field list.
Example of a self-join using the Employees table
To display the managers' names in the ReportsTo field instead of the managers' IDs, follow these steps.
-
In query Design view, add the Employees table to the query twice.
-
Rename the second table in the query.
Note Renaming copies of a table or query in a query doesn't rename the underlying table or query.
- Open a query in Design view.
- Click anywhere in the second copy of the field list for the table or query, and then click Properties
on the toolbar to display the property sheet. - In the Alias property box, type a new name for the table or query.
-
Create the join by dragging the ReportsTo field in the first table's field list to the EmployeeID field in the second table's field list.
-
Add the LastName, FirstName, and Title fields from the first field list to the design grid.
-
Add the LastName field from the second field list to the design grid. To display the field name as "Manager" instead of "LastName," set the Caption property in this field's property sheet.