MS-Access / Getting Started

---

Working with Multiple Related Tables

One powerful feature of queries is the ability to view related fields from different tables together in a query datasheet. For instance, using our database, we can create a query to list customer name and contact information with order dates and numbers, even though two different tables store the data. The relationship between the two tables is the ContactID field, which is the primary key of the Address Book table. The same field, ContactID, is in the Orders table - it identifies the customers who placed each order.

In order for Access to display data from different tables, a relationship must be defined between the tables. A relationship between tables is created in one of these ways:

• A lookup field exists, creating a relationship between two tables.
• A relationship was defined in the Relationships window. (Creating a lookup field automatically creates a corresponding relationship in the Relationships window.)
• Access automatically creates a relationship when it finds related fields in two tables - that is, if the two fields have the same name and data type, and one of the matching fields is the primary key of its table.
• You create a relationship in Design view when defining a query.

When a relationship exists between two tables displayed in Design view, the tables appear joined by a line.

If you use data from two tables that are not directly related, you have to make sure any other tables that relate the fields you want to display in the query datasheet, appear in the Query Design view.

If referential integrity is enforced, the "1" and "∞" symbols appear on the relationship line to denote the "one" and "many" sides of the relationship. If referential integrity is not enforced, those symbols do not appear on the line.

Joining tables in Design view

Although you can create or edit a relationship between two tables in Design view, remember: The relationship defined in Design view is used only for the query; it's not used in any other part of the database. You can use a type of join that you may not want to use in the database as a whole, but that you may find 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 the Delete key.)

To create a join, you use the Table pane of Design view and follow the same procedure you use when creating a relationship in the Relationship window - you first identify the two related fields (each in a different table) you want to join, and then you drag the field from one table to the related field in the other table.

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 see the Join Properties dialog box. 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 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, 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 is the default, meaning that if you don't set a join type, this 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 from the table on the left appear.
• Option # 3 (Right outer join): A query displaying records from both tables displays all records from the table on the right. From the table on the left, only records that have matching partners from the table on the right appear.

The line that connects two tables in the Relationships view (and in Design view as well) reflects information about how the tables are joined. The arrow points to the table that contributes matching records - all records from the other table display in the query datasheet.

If you create a sales report and want to see products that haven't sold at all, you want an outer join that shows all the products from the Products table, regardless of whether they appear in the Order Details table.

If you create a query with fields from two tables that don't have a relationship defined, Access doesn't know how to relate records, so every combination of records between the two tables displays in the datasheet. Generally (as you might expect) these queries won't give you meaningful results.