Access Hacks

Hack #53. Create an Outer Join

Access doesn't support making an outer join; here's a workaround.

The standard join between two tables returns records that match based on the field or fields being selected as keys. This is called an inner join. For example, a statement such as "give me all customers and their sales records" usually is interpreted to mean return all the sales records and the customers to whom those records belong.

Sometimes, though, a left or right join is requested. For example, "give me all our customers and any sales they might have had" is really a request for a left join. In other words, return all the records from the left table (the customers) and any sales records that go with them.

Figure 5-50 shows how records returned from a left join query look. In this example, there are more customers than purchase date records. Some of the customers have no purchases and therefore have no data in the column on the right.

A right join returns all records from the table on the right and only those records from the table on the left that match on the key. The three types of joins—inner, left, and right—are easy to set up. The inner one is the default when two tables are related.

You can set the join type in either the Relationships window or in a query design by double-clicking directly on the line between the two tables. Figure 5-51 shows the Join Properties dialog box that appears when the line is double-clicked. The dialog contains options for the three join ...