Part I: Access Building Blocks
122
Specifying the Join Type between tables
The right side of the Edit Relations window has four buttons:l Create: Clicking the Create button returns you to the Relationships window with the
changes specified.
l Cancel: The Cancel button cancels the current changes and returns you to the
Relationships window.
l Join Type: The Join Type button opens the Join Properties dialog box.l (^) Create New: The Create New button lets you specify an entirely new relation between the
two tables and fields.
By default, when you process a query on related tables, Access only returns records that appear in
both tables. Considering the payroll example from the “Integrity Rules” section, earlier in this
chapter, this means that you would only see employees that have valid paycheck records in the
paycheck table. You would not see any employees who have not yet received a paycheck. Such a
relationship is sometimes called an equi-join because the only records that appear are those that
exist on both sides of the relationship.
However, the equi-join is not the only type of join supported by Access. Click on the Join Type
button to open the Join Properties dialog box. The alternative settings in the Join Properties dialog
box allow you to specify that you prefer to see all the records from either the parent table or child
table, regardless of whether they’re matched on the other side. (It’s possible to have an unmatched
child record as long as the foreign key in the child table is null.) Such a join (called an outer join)
can be very useful because it accurately reflects the state of the data in the application.
In the case of the Collectible Mini Cars example, seeing all the customers, regardless of whether
they have records in the Sales table, is what you’re shooting for. To specify an outer join connect-
ing customers to sales, perform these steps:
- From the Relationships window, add tblCustomers and tblSales. Click the Join
Type button.
The Join Properties dialog box appears (see Figure 3.17).
- Select the Include ALL Records from ‘tblCustomers’ and Only Those
Records from ‘tblSales’ Where the Joined Fields Are Equal check box. - Click OK.
You’re returned to the Edit Relationships dialog box.
- Click OK.
You’re returned to the Relationships window. The Relationships window should now
show an arrow going from the Contacts table to the Sales table. At this point, you’re
ready to set referential integrity between the two tables on an outer join relationship.