Microsoft Office Tutorials and References
In Depth Information
Working with Multiple Related Tables
useful for a single query (which you may then use as the source data for a
form or report). You can also delete a relationship in Design view without
deleting the same relationship in the Relationships window. (To delete the
join, click the line and then press Delete.)
To create a join, use the Table pane of Design view, and follow the same
procedure you use for creating a relationship in the Relationship window.
First, identify the two related fields (each in a different table) you want to
join; then drag the field from one table to the related field in the other table.
Voilà — a join!
Choosing the type of join and setting join properties
You can edit the join properties of a relationship for the query in Design
view. To do so, double-click the relationship line to display the Join
Properties dialog box, shown in Figure 1-13.
If you have trouble double-clicking the relationship line, keep trying! The tip
of the pointer needs to be right on the line.
The new properties apply only in the current query and not in any other
objects in the database except those based on this query.
The Join Properties dialog box’s options are largely self-explanatory, but
using the dialog box effectively requires knowledge of a few buzzwords that
describe particular types of relationships but don’t appear in the dialog box.
The buzzwords inner join, left outer join, and right outer join are included in
the descriptions of the following three options:
✦ Option 1 (inner join): A query displaying records from both tables
displays only those records that have counterparts in the related table.
Records that don’t have matching partners in the opposite table are
hidden, as though they didn’t even exist. This setting is the default,
meaning that if you don’t set a join type, this type is what you get.
Option 2 (left outer join): A query displaying records from both tables
displays all records from the table on the left. From the table on the
right, only records that have matching partners in the table on the