Download (direct link):
These two tables are related because they share a common value.
BKEY ØÛÇØØØ 1
LasMa i â 1 oo LOTNUMBER
See the section, “Understanding Relationships in Queries,” later in this chapter, for more information on creating relationships if they don’t already exist in your database.
Adding Multiple Tables to Queries
In Chapter 2, I show you how to use both a Query Wizard and the Query Design view to create simple queries with a single table as the source of the data. Now I give you the lowdown on building queries that include fields from more than one table. (Using fields from another query works exactly the same way except that you choose a query instead of a table as the record source.)
Selecting multiple tables upfront
The method you use to select the tables for your query depends on the option you select to begin the query. Here’s a brief review of the two methods:
Chapter 5: Creating Multi-Table Queries
Query Wizard: If you decide to use the Query Wizard, you choose the first table from the drop-down Tables/Queries list box in the first section of the simple Query Wizard dialog box and then add the fields you want to use from that table to the query. Then you select the next table that you want to use and add its fields to the query. You continue this process until you’ve added all the tables and their fields to the query.
^ Design view: If you create your query in Design view, you select the tables for the query by using the Show Table dialog box. You can add more than one table at a time by selecting all the tables you want to use before you click the Add button (see Figure 5-2). Use Ctrl+click to add each table you click to the selection or Shift+click to add a contiguous range of tables to the selection. Click Close to close the Show Table dialog box after you’ve added the tables to the query.
Select all the tables you want to add to the query and click Add.
You can use whichever method you prefer to add the tables to the query. Adding the tables in Design view means that you still need to add the fields you want to the query (see Figure 5-3); on the other hand, you may prefer doing the Design view method because you can more easily control the order in which the fields appear in the query.
When you add the tables to the query in Design view, none of the fields are automatically added to the design grid.
100 Part II: Creating Effective Queries
Notice that in Figure 5-3 the links between the tables in the query automatically appear when you add those tables to the query design. In this case, the selected tables are the same ones shown in Figure 5-1, so the tables are linked the same way as they were in the earlier figure.
Sometimes you may see the link but won’t be able to see the fields that are related because not enough space is allocated to display all the fields in a table. You can display the linked fields by scrolling the field list with the scroll bar along the right side of the list of fields, or you can drag the edges of the list to expand the list so more fields are visible.
If you add multiple tables to a query and no links appear in Design view, you have to create the links before you can run the query. Access won’t allow you to save your work or run the query if the links don’t exist before you begin creating the query. One way to add the links is to drag the field name from one table to the other, making sure, of course, that you drop the field onto the field that it is supposed to link to in the second table.
Each table in a query must be linked to at least one other table in the query. It’s not necessary for every table to be linked directly to every other table, though. In fact, if you have three or more tables in a query and every table is linked to every other table directly, your database design is probably pretty messed up! (I show you a tool in Chapter 20 that may help you deal with this type of problem.)
Adding the tables you forgot about to your query
Inevitably as you start to create more complex multiple-table queries, you’ll discover that you need to add a bit more information, but that the information you need is stored in a table that you forgot to add to your query. This isn’t quite the disaster that it might seem because it’s pretty easy to add tables to a query that you’ve already started working on.
If the new table that you want to add already has an established table relationship with at least one of the tables in your query, you simply need to open the Show Table dialog box and add the new table. Follow these steps:
1. Choose QueryOShow Table or click the Show Table button on the toolbar.
The Show Table dialog box appears.
2. Add the new table by selecting it and clicking Add.
Chapter 5: Creating Multi-Table Queries 101
If the new table you want to add doesn’t have an established table relationship with one of the existing tables in the query (or if you’re not sure whether it does), things are a bit trickier. You need to establish that relationship (as discussed later in this chapter in the section “Understanding Relationships in Queries”) before adding the new table.