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 joinsinner, left, and rightare 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 types.

Figure 5-50. Returned records from a left join query

Figure 5-51. Setting join properties

As you can see, there is no option to create an outer join, which would return all the records that match, plus the records that don't match from both tables. The trick to doing this is to simply assemble the three types of available join queries into one query. This final query uses the Union operator to assemble the results from the other three queries.

A Union query works only with straight SQL statements. You can enter the SQL directly into a new query or, to make it easier, copy the generated SQL from the three join types and paste it into a new query. All you need to do is start the second and third mini-SQL Select statements with the Union operator in a new query, like this:

     SELECT tblCustomer2.LastName, tblSales2.PurchaseDate
     FROM tblCustomer2 INNER JOIN tblSales2 ON
     tblCustomer2.CustomerID = tblSales2.Customer_ID
     Union
     SELECT tblCustomer2.LastName, tblSales2.PurchaseDate
     FROM tblCustomer2 LEFT JOIN tblSales2 On
     tblCustomer2.CustomerID = tblSales2.Customer_ID
     Union
     SELECT tblCustomer2.LastName, tblSales2.PurchaseDate
     FROM tblCustomer2 RIGHT JOIN tblSales2 ON
     tblCustomer2.CustomerID = tblSales2.Customer_ID;

Figure 5-52 shows the result of running the SQL.

Figure 5-52. The results of an outer join

Both columns (each comes from a different table) have blankswhere there was no matching record in the other tableand the records that match are there as well.