Building Queries Based on Multiple Tables

If you have properly normalized your table data, you probably want to bring the data from your tables back together by using queries. Fortunately, you can do this quite easily by using Access queries.

The query in Figure 10.10 joins the Customers, Orders, and Order Details tables, pulling fields from each. Notice in the figure that I have selected the CustomerID and CompanyName fields from the Customers table, the OrderID and OrderDate fields from the Orders table, and the UnitPrice and Quantity fields from the Order Details table. After you run this query, you should see the results shown in Figure 10.11. Notice that you get a record in the query's result for every record in the Order Details table. In other words, there are 2,155 records in the Order Details table, and that's how many records appear in the query output. By creating a multitable query, you can look at data from related tables, along with the data from the Order Details table.

Figure 10.10. A query joining the `Customers`, `Orders`, and `Order Details` tables.

graphics/10fig10.jpg

Figure 10.11. The results of querying multiple tables.

graphics/10fig11.jpg

Hour 9 discusses how setting up the right type of relationship ensures ease of use in designing system objects. By setting up relationships between tables in a database, Access knows how to properly join them in the queries that you build.

To remove a table from a query, you click anywhere on the table in the top half of the query design grid and then press the Delete key. You can add tables to the query at any time by clicking the Show Table button on the toolbar. If you prefer, you can select the Database window and then click and drag tables directly from the Database window to the top half of the query design grid.

Pitfalls of Multitable Queries

You should be aware of some pitfalls of multitable queries: They involve updating as well as which records you see in the query output.

It's important to remember that you cannot update certain fields in a multitable query. You cannot update the join fields on the "one" side of a one-to-many relationship (unless you've activated the Cascade Update Referential Integrity feature). You also can't update the join field on the "many" side of a relationship after you've updated data on the "one" side. More importantly, which fields you can update, and the consequences of updating them, might surprise you. If you update the fields on the "one" side of a one-to-many relationship, you must be aware of that change's impact. You're actually updating that record in the original table on the "one"side of the relationship, and several records on the "many" side of the relationship may be affected.

For example, Figure 10.12 shows the result of a query based on the Customers, Orders, and Order Details tables. I have changed Alfreds Futterkiste to Waldo Futterkiste on a specific record of the query output. You might expect this change to affect only that specific order detail item. However, pressing the down-arrow key to move off the record shows that all records associated with Alfreds Futterkiste are changed (See Figure 10.13). This happens because all the orders for Alfreds Futterkiste were actually getting their information from one record in the Customers table—the record for customer ID ALFKI—and that is the record I modified while viewing the query result.

Figure 10.12. Changing a record on the "one" side of a one-to-many relationship.

graphics/10fig12.jpg

Figure 10.13. The result of changing a record on the "one" side of a one-to-many relationship.

graphics/10fig13.jpg

The second pitfall of multitable queries has to do with figuring out which records result from a multitable query. So far, you have learned how to build only inner joins. Hour 15 covers join types in detail, but for now, you need to understand that the query output contains only customers who have orders and orders that have order details. This means that not all the customers or orders might be listed. In Hour 15, you'll learn how to build queries in which you can list all customers, regardless of whether they have orders. You'll also learn how to list only the customers that do not have orders.

AutoLookup in Multitable Queries

The AutoLookup feature is automatically available in Access. As you fill in key values on the "many" side of a one-to-many relationship in a multitable query, Access automatically looks up the non-key values in the parent table. Most database developers refer to this as enforced referential integrity. A foreign key must first exist on the "one" side of the query to be entered successfully on the "many" side. As you can imagine, you don't want to be able to add to a database an order for a nonexistent customer.

For example, I have based the query in Figure 10.14 on the Customers and Orders tables. The fields included in the query are CustomerID from the Orders table; CompanyName, Address, and City from the Customers table; and OrderID and OrderDate from the Orders table. If you change the CustomerID field associated with an order, Access looks up the CompanyName, Address, and City fields from the Customers table and immediately displays them in the query result.

Figure 10.14. Using AutoLookup in a query with multiple tables.

graphics/10fig14.jpg

Notice in Figure 10.15 how the information for Alfreds Futterkiste is displayed in the query result. Figure 10.16 shows that the CompanyName and Address fields change automatically when the CustomerID field is changed to Around the Horn. Don't be confused by the combo box used to select the customer ID. The presence of the combo box within the query is a result of Access's Lookup feature, covered in Hour 14, "Power Table Techniques." The customer ID associated with a particular order is actually being modified in the query. If you add a new record to the query, Access fills in the customer information as soon as you select the customer ID associated with the order.

Figure 10.15. A query result before another customer ID is selected.

graphics/10fig15.jpg

Figure 10.16. The result of an auto-lookup after the customer ID is changed.

graphics/10fig16.jpg

Task: Working with Multitable Queries

Build a query that combines information from the Customers, Orders, and Order Details tables. To do this, follow these steps:

Select the Query tab from the Database window.
Click New.
Select Design View.
From the Show Table dialog box, select Customers, Orders, and Order Details by holding down the Ctrl key and clicking each table name. Then select Add.
Click Close.
Some of the tables included in the query might be hiding below the visible area of the window. If this is the case, scroll down with the vertical scrollbar to view any tables that aren't visible. Notice the join lines between the tables; they're based on the relationships set up in the Relationships window.

Select the following fields from each table:

Customers: Country, City
Orders: Order Date
Order Details: UnitPrice, Quantity

Sort by Country field and then City field. The finished query design should look like the one in Figure 10.17.

Figure 10.17. A sample query design.
Run the query. Data from all three tables should be included in the query output (see Figure 10.18).

Figure 10.18. The Query output.
Try changing the data in the City field for one of the records in the query result. Notice that Access appears to modify the data for the record (as well as several other records). This happens because the City field actually represents data from the "one" side of the one-to-many relationship. In other words, when you're viewing the Country and City fields for several records in the query output, the data for the fields might originate from one record. The same goes for the Order Date field because it's also on the "one" side of a one-to-many relationship.
Practice modifying the data in the query result, and then return to the original table and notice which data has changed.

[ Team LiB ]