Part I: Access Building Blocks
166
It’s possible to have a buyer in tblCustomers who has no sales. With referential integrity control-
ling the relationship, it’s impossible, to have a sale with no buyer. If you create a query to show con-
tacts and their sales, any record of a contact without a sale is not shown in the returned recordset.
Understanding outer joins
The query design window should now display two tables in the top pane of the query window —
tblCustomers and tblSales, with four fields selected to display. If your query window does
not have these two tables, create a new query and add them. The following sections use these
tables as examples to explain how inner and outer joins operate.
Unlike inner joins (equi-joins), outer joins show all records in one table and any matching records
in the other. The table or query that does not have a matching record simply displays an empty cell
for the unmatched data when the recordset is displayed.
When you’ve created an outer join, the join line points to one of the tables. The base of the arrow
is attached to the “main” table — the one that returns all records. The arrow points to the right-
joined (or left-joined) table — the one that might be missing a matching record (see Figure 4.29).
So far, the outer-join examples you’ve seen have involved tables with no formal relationships.
Figure 4.29 shows the results of an inner join between contacts and sales. Not all customers have
placed sales with Collectible Mini Cars — perhaps they’ve called for product quotes, but haven’t
placed an order yet.
The recordset contains all customers, regardless of whether they’ve placed sales.
Once in the query design, again double-click the join line between the tblCustomers and tbl-
Sales tables. Select the second choice from the Join Properties dialog box (Include All Records
from tblCustomers) and click the OK button. The join line now has an arrow pointing to the right
toward tblSales; this is a right outer join. (If the arrow points to the left in the top pane, the join
is known as a left outer join.)
If you create this right outer join query between the tables and select the Datasheet button to dis-
play the recordset, you see that you have a few more records than in an inner-join query. This
means that there are at least a few records in tblCustomers without sales.
A sales record without a customer is called an orphan record. Referential integrity can’t be set in the
Relationships window if orphan records exist. If you can’t set referential integrity between tables,
you’ll have to identify and remove orphan records before trying again.
