Section 6.3. Queries and Related Tables | Access 2007[c] The Missing Manual

6.3. Queries and Related Tables

In Chapter 5, you learned how to split data down into fundamental pieces and store it in distinct, well-organized tables. This sort of design's only problem's that it's more difficult to get the full picture when you have related data stored in separate places. Fortunately, Access has the perfect solutionyou can bring the tables back together for display using a join .

A join's a query operation that pulls columns from two tables and fuses them together in one grid of results. You use joins to amplify child tables by adding information from the parent table. Here are some examples:

In the bobblehead database, you can show a list of bobblehead dolls (drawn from the child table Dolls ) along with the manufacturer information for each doll (from the parent table Manufacturers).
In the Cacophon music school database, you can get a list of available classes, with instructor information.
In the Boutique Fudge database, you can get a list of orders, complete with the details for the customer who placed the order.

Note: You've already learned how to create lookup tables to show just a bit of information from a linked table. A lookup can show the name of a product category in place of the ID number in the ProductID field. However, a join query's far more powerful. It can grab oodles of information from the linked tablefar more than you could fit in a single field.

Figure 6-16 shows how a table join works.

Figure 6-16. On its own, the Classes table tells you about each class, but it gives each class, but it gives you only the ID of the assigned instructor. But join this table to the Teachers table, and you can get any other details from the linked teacher recordincluding the first and last name. You'll see how to build this example in Section 6.3.3.

6.3.1. Joining Tables in a Query

Access makes it remarkably easy to join two tables. The first step's adding both tables to your query, using the Show Table dialog box. If you're creating a new query in Design view, then the Show Table dialog appears right away. If you're working with a query you've already created, then make sure you're in Design view, right-click the window, and then choose Show Table.

If you've already defined a relationship between the two tables (using the relation-ships window, as described in Section 5.2.1, or by creating a lookup, as described in Section 5.2.5), then Access uses that relationship to automatically create a query join. You'll see a line on the diagram that connects the appropriate fields, as shown in Figure 6-17.

If you haven't already defined a relationship between the two related tables, then you probably should, before you create your query (see Chapter 5 for full instructions). But if for some cryptic reason you've decided not to create the relationship (perhaps the database design was set in stone by another, less savvy Access designer), then you can manually define the join in the query window. To do so, just drag the linked field in one table to the matching field in the other table. You can also remove a join by right-clicking the line between the tables, and then choosing Delete.

Figure 6-17. Access automatically joins the CustomerID field in the Orders table to the ID field in the Customers table, based on the relationship that's defined in the database.

Note: If you add two unrelated tables, then Access tries to help you out by guessing a relationship. If it spots a field with the same data type and the same name in both tables, then it adds a join on this field. This action often isn't what you wantfor example, many tables share a common ID field. Also, if you're following the database design rules from Section 2.5, then your linked fields have slightly different names in each table, like ID and CustomerID. If you run into a problem where Access assumes a relationship that doesn't exist, then just remove it before adding the join you really want.

UP TO SPEED
Relationships vs. Joins

It's important to understand the differences between a relationship and a query join.

Relationship . A permanent link between two tables, which is stored in your database. When creating a relationship in the database, you have the option of switching on referential integrity: a set of rules that prevents inconsistent data in related tables (Section 5.2.3).
Join . A query feature that lets you combine related data from two tables into one set of results. The join doesn't affect how you enter or edit that information in the underlying tables.

If you have a relationship in place, then Access assumes you'll want to use a join to link those tables together in a query, which only makes sense.

Once you have your two tables in the query design window and you've defined the join, then you're ready to choose the fields you want. You can pick fields from both tables. You can also add filter conditions and supply a sort order, as you would with any other query. Figure 6-18 shows an example of a query that uses a join, and Figure 6-19 shows that same query in action.

Note: When you have two linked tables, it's easy to forget what you're showing. If you join the Orders and Customers tables, and then select fields from each, then what do you end up with: a list of classes or a list of instructors? Easy, you get a list of orders, complete with customer information. Queries with linked tables always act on the child table and bring in additional information from the parent.

Figure 6-18. This query shows information from the Orders and Customers tables. It doesn't matter whether the first field's from the Orders or Customers tableeither way, you're creating a list of orders with added customer information. Notice how the Table box (under the Field box) shows which table each field comes from.

Figure 6-19. You can easily see at a glance who ordered what. The ID column's the order ID (although you could display the IDs from both the Customers and Orders tables).

Note: When you perform a join, you see repeated information. If you join the Customers and Orders tables, you see the first and last name of a shopaholic customer appear next to several orders. However, this doesn't violate the database rule against duplicate data. Even though the customer details appear in more than one place in the query results, they're stored only once in the Customers table.

Remember, when you link a parent and child table with a join query, you're really performing a query that gets all the records from the child table, and then adds extra information from the parent table. For example, you can use a join query to get a list of orders (from the child table) and supplement each record with information about the customer that made the order. No matter how you create the join, you won't ever get a list of customers with order information tacked onthat wouldn't make sense, because every customer can make multiple orders.

Joins are one of the most useful features in any query writer's toolkit. They let you display one table that has all the information you need.

Note: When using more than one table, there's always a risk that two tables have a field with the same name. This possibility isn't a problem if you don't plan to show these fields in your query, but it can cause confusion if you do. One way to distinguish between the two fields is to rename one of them in the query datasheet. You'll learn how to perform this trick with a calculated field in Section 7.1.2.

UP TO SPEED
Modifying Information Using a Join Query

You need to be careful when modifying the data in a query that uses a join. There's never a problem if you want to modify the details from the child table. In the example in Figure 6-19, it's easy enough to change the DatePlaced or StatusID fields to change the order record.

However, consider what happens if you change one of the values in the parent table, like the customer's first or last name. Obviously, the same customer information may appear several times in the query. (For example, the query in Figure 6-19 shows two orders by a customer named Toby.) If you modify the customer name in one place, then Access automatically changes the information in the Customers table, and then refreshes the entire query. So, if you change "Toby" to "Tony" in Figure 6-19, then Access refreshes the second and third rows of the datasheet.

A potential problem occurs if you want to change the link between the order record and the customer record. You may want to edit an order that's assigned to Toby so that the database says Lisa made the order. However, you can't make this change by editing the FirstName and LastName fields in the query. (If you do, you'll simply wind up changing Toby's record in the Customers table.) Instead, you need to change the CustomerID field in the Orders table so that it points to the right person. In the query shown in Figure 6-19, the CustomerID field isn't included, so there's no way to change the link.

6.3.2. Outer Joins

The queries you saw in the previous example use what database nerds call an inner join . Inner joins show only linked recordsin other words, records that appear in both tables. If you perform a query on the Customers and Orders tables, then you don't see customers that haven't placed an order. You also don't see orders that aren't linked to any particular customer (the CustomerID value's blank) or aren't linked to a valid record (they contain a CustomerID value that doesn't match up to any record in the Customers table).

Outer joins are more accommodatingthese joins include all the same results you'd see in an inner join, plus the leftover unlinked records from one of the two tables (it's your choice). Obviously, these unlinked records show up in the query results with some blank values, which correspond to the missing information that the other table would supply.

Suppose you perform an outer join between the Orders and Customers tables, and then configure it so that all the order records are shown. Any orders that aren't linked to a customer record appear at the bottom of the list, and have blank values in all the customer-related fields (like FirstName and LastName):

Table 6-1.

FirstName	LastName	ID	DatePlaced	StatusID
Stanley	Lem	7	13-Jun-07	Cancelled
Toby	Grayson	4	03-Nov-06	Returned
Toby	Grayson	6	03-Nov-06	Shipped
		18	01-Jan-08	In Progress
		19	01-Jan-08	In Progress

In this particular example, it doesn't make sense for orders that aren't linked to a customer to exist. (In fact, it probably indicates an order that was entered incorrectly.) However, if you suspect a problem, an outer join can help you track down the problem.

Tip: You can prevent orphaned order records altogether by making CustomerID a required value (Section 4.1.1) and enforcing relational integrity (Section 5.2.3).

You can also perform an outer join between the Orders and Customers tables that shows all the customer records. In this case, at the end of the query results, you'll see every unlinked customer record, with the corresponding order fields left blank:

Table 6-2.

FirstName	LastName	ID	DatePlaced	StatusID
Stanley	Lem	7	13-Jun-07	Cancelled
Toby	Grayson	4	03-Nov-06	Returned
Toby	Grayson	6	03-Nov-06	Shipped
Ben	Samatara
Goosey	Mason
Tabasoum	Khan

In this case, the outer join query picks up three stragglers.

So how do you add an outer join to your query? Your start with an inner join (which Access usually adds automatically; see Section 6.3.1), and then convert it to an outer join. To do so, just right-click the join line that links the two tables in the design window, and then choose Join Properties (or just double-click the line). The Join Properties dialog box (Figure 6-20) appears, and lets you change the type of join you're using.

Figure 6-20. The first option, "Only include rows where the joined fields from both tables are equal", performs the standard inner join. The other two options let you create an outer join that incorporates all the unlinked rows from one of the two tables.

6.3.2.1. Finding unmatched records

Inner joins are by far the most common joins. However, outer joins let you create at least one valuable type of query: a query that can track down unmatched records.

You've already seen how an outer join lets you see a list of all your orders, plus the customers that haven't made any orders. That combination isn't terribly useful. However, the marketing department's already salivating over the second part of this equationthe list of people who haven't bought anything. This information could help them target a first-time-buyer promotion.

To craft this query, you start with the outer-join query that includes all the customer records. Then, you simply add one more ingredient: a filter condition that matches records that don't have an order ID. Technically, these are considered null (empty) values.

Here's the filter condition you need, which you must place in the Criteria box for the ID field of the Orders table:

 Is Null

Now, when Access performs the query, it includes only the customer records that aren't linked to anything in the orders table. Figure 6-21 shows the query in Design view.

Figure 6-21. This query combines an outer join with a filter condition that matches only unlinked customer records. Notice the Show checkbox isn't checked. That's because the ID field's used for a filter condition, but there's no point in displaying it in the results datasheet.

6.3.3. Multiple Joins

Just as you're getting comfortable with inner and outer joins, Access has another feature to throw your way. Many queries don't stop at a single join. Instead, they use three, four, or more to bring multiple related tables into the mix.

Although this sounds complicated at first, it really isn't. Multiple joins are simply ways of bringing more related information into your query. Each join works the same in a multiple-join situation as it does when you use it on its own. To use multiple joins, just add all the tables you want from the Show Table dialog box, make sure the join lines appear, and then choose the fields you want. Access is almost always intelligent enough to figure out what you're trying to do.

Figure 6-22 shows an example where a child table has two parents that can both contribute some extra information.

Sometimes, the information you want's more than one table away. Consider the OrderDetails table Boutique Fudge uses to list each item in a customer's order. On its own, the OrderDetails doesn't provide a link to the customer who ordered the item, but it does provide a link to the related order record. (See Section 5.4.2.2 for a discussion of this design.) If you want to get the information about who ordered each item, you need to add the OrderDetails, Orders, and Customers table to your query, as shown in Figure 6-23.

Figure 6-22. In this example, a list of products is amplified with product category information and supplier information. The Products table's a child of both the ProductCategories and Suppliers tables, so this query uses both tables effortlessly.

Figure 6-23. If you want to find out who ordered each item, then you need to find the linked Orders table, and then continue to the linked Customers table. Even if you don't want to show any data from the Orders table, you still need to follow this two-step process. The top figure shows the query that does this, and the bottom figure shows the result you'll get when you run the query.

Multiple joins are also the ticket if you have a many-to-many relationship with a junction table (Section 5.3.2.1), like the one between teachers and classes. As you'll remember from Chapter 5 (Section 5.4.1), the Cacophon Studios music school uses an intermediary table to track teacher class assignment. If you want to get a list of classes, complete with instructor names, then you need to create a query with three tables: Classes, Teachers, and Teachers_Classes (see Figure 6-24).

Figure 6-24. Here's how you define a list of classes that includes the name of the assigned teacher next to each class. The top query shows the design you need, and the bottom figure shows the result.