Microsoft Access 2010 Bible

Chapter 4: Selecting Data with Queries

163

The problem in Figure 4.26 is that you can’t even tell records are missing. The only way you’d ever

determine that there should be more than 78 records returned by this query is by carefully exam-

ining the sales records, by composing another query that counts all sales, or by performing some

other audit operation.

You must modify the join characteristics between tblProducts and tblCategories to get an

accurate picture of the Collectible Mini Cars sales. Carefully right-click on the thin join line

between tblProducts and tblCategories, and select the Join Properties command from the

shortcut menu. This action opens the Join Properties dialog box (see Figure 4.27), enabling you to

specify an alternate join between the tables.

FIGURE 4.27

Selecting an outer join for the query

Equi-join

Left outer join

Right outer join

In Figure 4.27, the third option (Include All Records from ‘tblProducts’.. .) has been selected (the

first option is the default). Options 2 and 3 are called outer joins and direct Access to retrieve all

records from the left (or right) table involved in the join, regardless of whether those records are

matched on the other side of the join.

Figure 4.28 shows the result of the new join. In the lower-right corner of this figure you see how

an outer join appears in the Access query design, while the rest of the figure shows the recordset

returned by the query.

An outer join is represented by a join line with an arrow pointing at one of the tables involved in

the join. In Figure 4.28, tblProducts is right-joined to tblCategories, which means all

records from tblProducts are shown, regardless of whether there are matching records in tbl-

Categories. A blank cell in the Category column indicates a product for which no category has

been assigned.