Access 2010: The Missing Manual

Queries and Related Tables

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

A join is 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 is far more powerful. It can grab oodles of information from the linked table—far 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, ...