Microsoft Access 2010 Bible

Part I: Access Building Blocks

164

FIGURE 4.28

A right outer join corrects the “missing products” problem in Figure 4.27.

The lower portion of Figure 4.28 shows the recordset from the query. Notice that 88 records are

now returned, and that several rows in the recordset have no Category value. The query now

accurately reports the number of sales records.

Of course, you can easily create joins that make no sense, but when you view the data, it’ll be

pretty obvious that you got the join wrong. If two joined fields have no values in common, you’ll

have a datasheet in which no records are selected.

Note
You can select either table first when you create a join.

You would never want to create a meaningless join. For example, you wouldn’t want to join the

City field from the tblCustomer table to the tblSalesDate of tblSales. Although Access

enables you to create this join, the resulting recordset will have no records in it.

Deleting joins

To delete a join line between two tables, select the join line and press the Delete key. Select the

join line by placing the mouse pointer on any part of the line and clicking once.

Caution
If you delete a join between two tables and the tables remain in the query window unjoined to any other
tables, the solution will have unexpected results because of the Cartesian product that Access creates from the
two tables. The Cartesian product is effective for only this query. The underlying relationship remains intact.

Access enables you to create multiple-field joins between tables (more than one line can be drawn).

The two fields must have data in common; if not, the query won’t find any records to display.