- Relational Database Fundamentals
- Types of Relational Models
- Enforcing Referential Integrity
- Establishing Table Relationships
- Working with Multiple Tables in a Query
- Creating Other Types of Joins
- Creating a Unique Values Query
This chapter is from the book
This chapter is from the book
This chapter is from the book
Working with Multiple Tables in a Query
With a properly constructed relational database model, you'll end up with fields that don't make much sense by themselves. For example, the Northwind database has an Order Details table that includes a ProductID field—a foreign key from the Products table. This field contains only numbers and therefore by itself is meaningless to an observer.
The idea behind a multiple-table query is to join related tables and by doing so create a dynaset that replaces meaningless data (such as a product ID) with meaningful data (such as a product name).
The good news is that after you've established a relationship between two tables, Access handles everything else behind the scenes, so working with multiple tables isn't much harder than working with single tables.
Adding Multiple Tables to a Query
To add multiple tables to a query, follow these steps:
-
Display the Show Table dialog box. You have two choices:
-
If you're starting a new query without highlighting a table in advance, wait until you see the Show Table dialog box onscreen.
-
If you're already in the query design window, choose Query, Show Table (or click the toolbar's Show Table button).
-
Click the table name and then choose Add.
-
Repeat step 2 to add other tables, as necessary.
-
Click Close.
As you can see in Figure 3.5, Access displays join lines between related tables.
){kind=small}
Figure 3.5
When you add multiple, related tables to the query design window, Access automatically displays the join lines for the related fields.
Adding Fields from Multiple Tables
With your tables added to the query design window, adding fields to the query is only slightly different than adding them for a single-table query:
-
You can still add any field by clicking and dragging it from the table pane to one of the Field cells in the design grid.
-
When you choose a field directly from a Field drop-down list, note that the field names are preceded by the table name (for example, Products.SupplierID).
-
To lessen the clutter in the Field cells, first use the Table cell to choose the table that contains the field you want. After you do this, the list in the corresponding Field cell will display only the fields from the selected table.
From here, you can set up the query criteria, sorting, top N values, and calculated columns exactly as you can with a single-table query. Figure 3.6 shows a query based on the Products, Order Details, and Orders tables. The query shows the SupplierID, ProductName, and UnitsInStock (from Products), the Quantity (from Order Details), and OrderDate (from Orders), and a Left In Stock calculated column that subtracts the Quantity from the UnitsInStock. The dynaset will contain just those orders from May 6, 1998 and is sorted on the LeftInStock calculated column. Figure 3.7 shows the resulting dynaset.
){kind=small}
Figure 3.6
A query with three related tables that includes fields from all the tables.
){kind=small}
Figure 3.7
The dynaset returned by the multiple-table query shown in Figure 3.6.
NOTE
In the query shown in Figure 3.6, the Products and Orders table are said to have an indirect relationship. That is, they're related to each other, but only via the Order Details table. Note that it's possible to construct a query that only includes fields from Products and Orders, but you must still include the Order Details table in the query design to allow Access to set up the indirect relationship.
The only thing you have to watch out for is dealing with tables that each have a field with the same name. For example, both the Order Details table and the Products table have a UnitPrice field. To differentiate between them in, say, an expression for a calculated column, you need to preface the field name with the table name, like so:
For example, consider the formula that calculates the ExtendedPrice field in the Order Details Extended query. The idea behind this formula is to multiply the unit price times the quantity ordered and subtract the discount. Here's the formula:
To differentiate between the UnitPrice field in the Order Details table and the UnitPrice field in the Products table, the formula uses the term [Order Details].[UnitPrice], as shown in Figure 3.8.
){kind=small}
Figure 3.8
When the tables in a multiple-table query share a common field name, precede the field name with the table name in an expression.
Nesting Queries Within Queries
In the previous section I showed you how to add multiple tables to a query, but there's no reason why you can't also add other queries to the table pane. After all, the Show Table dialog box has a Queries tab that lists all your saved queries, so you can add them to the query design as easily as you add a table. When you nest one query inside another, Access runs the nested query first, and then uses the resulting dynaset to produce the rest of the query.
For example, in the Orders for May 6, 1998 query from the previous section, the Left In Stock calculated column returned the number of units each product had left in stock after subtracting the order quantity. Suppose you then wanted a new query that checked for those products with a negative Left In Stock value and returned the appropriate supplier data so that the product could be reordered.
To do this, you begin by adding both the Suppliers table and the Orders for May 6, 1998 query to the query design window. As you can see in Figure 3.9, Access automatically sets up a temporary relation between the common SupplierID fields (because SuppliersID is the primary key of the Suppliers table). The rest of the query is created in the usual way, and the result is shown in Figure 3.10.
){kind=small}
Figure 3.9
You can nest one query inside another and Access will set up a temporary relation based on a common field.
){kind=small}
Figure 3.10
The dynaset produced by the query in Figure 3.9.
Joining Tables Within the Query Design Window
As you've seen, Access recognizes existing relationships in multiple-table queries, and will also set up a temporary relationship if the common field is a primary key in one of the tables.
In other cases, you can have fields with common or similar data, but there's no existing relationship and Access doesn't set up a temporary relationship. For example, both Northwind's Customers and Suppliers tables have a City field. How can you create an inner join on this common field (to, for example, see which customers and suppliers are located in the same city)?
You can do this by creating a temporary relationship. After you've added the tables to the query design window's table pane, click and drag the field from one table and drop it on the related field in the other table. Access displays a temporary join line between the fields, as shown in Figure 3.11.
){kind=small}
Figure 3.11
To create a temporary relationship by hand, click and drag the field from one table and drop it on the related field in the other table.