| There are both inner joins and outer joins. Inner joins, which are also known as equi-joins, are by far the most common. All the queries you have done used an inner join. An inner join retrieves records only when there are equal values (that is, matching data) in the common field in the tables. As you saw, if you have suppliers in one table and merchandise in another, and a common SupplierID in both, you can use an inner join to create a query that matches merchandise data with supplier data. When you create a multitable query, an inner join is the default. If you want to create queries in which only records with matching data are retrieved, you don't have to worry what type of join you're using. Introducing Outer Joins What are outer joins? In an outer join, all the records from one table and only those records from the other table in which there's matching data are retrieved. Because all the records are retrieved from one table, you can find which of them don't have matching records in the other table. Outer joins work nicely in auditing and other exception-to-the-rule tests that ask "What's wrong with this picture?" questions. For example, if a firm is included in the customers table, why don't you see any orders from them? Or imagine the case of a supplier with no products. If a firm is truly a designated supplier, why aren't any items from its product line in your inventory? In most cases, there will be entirely reasonable explanations: Customers might be approved well before they place any orders; the firm might only occasionally use many of its varied suppliers. But these are the type of questions outer joins can answer. A Multitable Query Using an Outer Join Suppose Nifty Lions's owner wants to see if there are any customers without any orders. You'll create a multitable query using an outer join to find these records. 1. | Open the tblCustomers table and review the data.
Note that there is only one record for every customer. The primary key for the Customers table is CustomerID.
| 2. | Close the tblCustomers table. Open the tblOrders table and review the data.
| 3. | Select the Customer column. Click Sort Ascending.
Notice that although each customer could appear only once in the Customers table, it can appear many times in the Orders table.
| 4. | Close the tblOrders table without saving your design changes.
| 5. | Choose Tools, Relationships to open the Relationships window. Find the Customers and Orders tables.
There is a one-to-many relationship between the two tables through the CustomerID field.
| 6. | Close the Relationships window.
| 7. | In the Database window, click Queries and click New. With Design View chosen in the New Query dialog box, click OK.
| 8. | Add the tblCustomers and tblOrders tables. Close the Show Table dialog box.
Note that you see the same line between the field lists that you saw in the Relationships window. These two tables are joined through the CustomerID field, and you can perform a multitable query.
| 9. | Double-click CustLastName and CustFirstName from the Customers table to add them to the design grid.
| 10. | Double-click OrderID from the Orders table to add it as well.
| 11. | Save the query as qryNoOrderCustomers.
| 12. | Double-click the join line between the two field lists to open the Join Properties dialog box.
In the Join Properties dialog box, both tables are included, along with the CustomerID through which they are joined.
The current join is the default inner join. Access will only include records where the joined fieldsnamely, CustomerIDare equal.
|
Q&A | Q1: | Why does the dialog box refer to the "left table name" and "right table name"? Do those names get switched as I move the field lists in the query design window? | | A1: | No, they refer to the order in which the tables are named in the Structured Query Language (SQL) statement. You might also see the terms "left outer join" and "right outer join." I discuss SQL shortly. |
|
To compare an inner with an outer join, run the query as you would usually do. 1. | Close the Join Properties dialog box. Click View.
Access has used the CustomerID field to match customers with orders. In the Record Selector area, note that there are 33 records in your results.
| 2. | Click View to return to Design view. Right-click the join line between the two field lists. Select Join Properties to open the Join Properties dialog box.
| 3. | Change the selection to 2 (see Figure 8.12).
Figure 8.12. In the Join Properties dialog box, you select the join type. 
You're now using an outer join. Access retrieves all the customer records and only those records from the Orders table in which the CustomerID fields are equal.
Don't be deceived by this "only-ness" of the outer join. Only sounds like relatively few records will be matches. In fact, in many companies, most customers are "live" and have orders. So for most of the records, there will be a match, and nearly all your results will include records from both Customers and Orders tables.
| 4. | Click OK and click View to see the records.
Note that are now 44 records in the datasheet, 11 more than when you used an inner join. That means that there must be at least 11 customers who have not yet made orders.
| 5. | Select the OrderID field and click Ascending Sort. The customers without orders are listed first.
| 6. | Click View to return to Design view.
TIP Do you recall the Is Null operator? You can use it to display only those customers with no orders. In the Criteria row of the OrderID column, type Is Null. You can then click View to find customers with no matches (no orders). | 7. | Right-click the join line and choose Join Properties.
Take a look at option 3 in the dialog box. When would you use this type of outer join? In this case, Access retrieves all the Orders records and only those records in the Customers table in which there are matching customers. But as I've noted, don't be misled by the word only. There should be matches for all the recordswhat kind of order doesn't have a customer? In the Nifty Lions database, all orders have customers.
As you can see, though, this is just the kind of "What's wrong with this picture?" question that an auditor or manager might want to ask. If an order doesn't have an approved customer, that could indicate some type of criminal activity.
| 8. | Close the query and save your changes.
|
Find Unmatched Query Wizard If you find all this business about innies and outies confusing, Access provides the easy-to-use Find Unmatched Query Wizard as a substitute for many outer-join queries. Click Queries in the Database window, click New, and choose Find Unmatched Query Wizard in the New Query window. If you understood the previous example, you'll have no trouble dealing with the wizard. Here's an overview of its five screens: Select the table from which you want to retrieve all the records. In the example where you found customers without orders, that is the Customers table. Select the table from which you want to find "only" those records with matching data. In the example, that is the Orders table. Select the field with matching data. Access usually makes it easy for you. In this example, it selects CustomerID for both tables (see Figure 8.13). Figure 8.13. This dialog box in the Find Unmatched Query Wizard specifies the field with matching data. 
Select which fields you want to see in the query. These are fields from the "all" table. In the example, you used the CustLastName and CustFirstName fields. Name your query and run it.
Multitable Queries Conclusion Just one final word about multitable queries. Although outer joins are indeed useful, there's a reason that inner joins are the default join. Most of the queries you'll do likely will require an inner join. So even if you don't fully understand the difference between inner and outer joins, remember that the type of join you probably want has already been selected for you. |
