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:
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.
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.
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.
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.
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.
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):
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:
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.
188.8.131.52. 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:
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.
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 184.108.40.206 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.
Multiple joins are also the ticket if you have a many-to-many relationship with a junction table (Section 220.127.116.11), 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).