Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

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 with Access queries.

The query in Figure 4.10 joins the Customers, Orders, and Order Details tables, pulling fields from each. Notice that the CustomerID and CompanyName fields are selected from the Customers table, the OrderID and OrderDate from the Orders table, and the UnitPrice and Quantity from the Order Details table. After running this query, you should see the results shown in Figure 4.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.

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

NOTE

To remove a table from a query, click anywhere on the table in the top half of the query design grid and press the Delete key. You can add tables to the query at any time by clicking the Show Table button from 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.Chapter 3, "Relationships: Your Key to Data Integrity."

For example, Figure 4.13 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 my query output. You might expect this change to affect only that specific order detail item. Pressing the down-arrow key to move off the record shows that all records associated with Alfreds Futterkiste have been changed (see Figure 4.14). This happened because all the orders for Alfreds Futterkiste were actually getting their information from one record in the Customers tablethe record for customer ID ALFKI. This is the record I modified while viewing the query result.

To get this experience firsthand, try changing the data in the City field for one of the records in the query result. Notice that the record (as well as several other records) is modified. 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. The only field in the query output that can't be modified is TotalPrice, a calculated field. Practice modifying the data in the query result, and then returning to the original table and noticing which data has changed.

The second pitfall of multitable queries is figuring out which records result from such a query. So far, you have learned how to build only inner joins. Join types are covered in detail in Chapter 11, but for now, it's important to understand that the query output contains only customers who have orders, and orders that have order detail. This means that not all the customers or orders may be listed. In Chapter 11, 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 without orders.

Row Fix-Up in Multitable Queries

The Row Fix-Up feature is automatically available to you in Access. As you fill in key values on the "many" side of a one-to-many relationship in a multitable query, the non-key values are automatically looked up 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 an order to your database for a nonexistent customer.Chapter 2, "What Every Developer Needs to Know About Databases and Tables." The customer ID associated with a particular order is actually being modified in the query. If the user adds a new record to the query, Access fills in the customer information as soon as the user selects the customer ID associated with the order.