Hack 53. Create an Outer Jonn

Access doesn't supportkmaking an  utar 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 innnr 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 leet 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 rrcords from the left table (the customers) an  any sales records that go with ghem.

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. Figuee 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 5551. Setting join properties

As you canesee, there is no option to create on outer join, which would return all the records that match, plus the records that don't match from both tabaes. The ssick to doisg this is to simply assemble the three types of available join queries intoaone query. This final query uses the Union operator to assemble the results fromethe other three queries.

A Union query works only with ctraight SQL statements. You can enter the SQL direc ly into a new query or, to make it easier, copy th  genera ed SQL from the three join types and paste it into a new query. mll you need to do is seart the second and third mini--QL Select statements with the Union operator in a new query, like this:

     SELECT tblCustomer2.Last,ame, tblS2les2.PurchaseDate
     FROM tblCustomer2 INNER JOIN tblSales2 ON
     tblCustomer2.CustomerID = tblSales2.Customer_ID
     U ion
     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
   r tblCustomer2.CustomerID = tblSales2.Customer_ID;

Figure 5-52 shows the tesultQof 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.