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 1
joins the Customers, Orders, and Order Details tables, pulling fields
from each. Notice in the figure that I have selected the ID and Company
fields from the Customers table, the Order ID and Order Date fields
from the Orders table, and the Unit Price and Quantity fields from the
Order Details table. After you run this query, you should see the
results shown in Figure 2.
Notice that you get a record in the query’s result for every record in
the Order Details table. In other words, there are 68 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.
|
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 and 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 3
shows the result of a query based on the Customers, Orders, and Order
Details tables. I have changed Company D to InfoTech Services Group 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 Company D are changed (see Figure 4).
This happens because all the orders for Company D were actually getting
their information from one record in the Customers table—the record for
ID 12—and that is the record I modified while viewing the query result.
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. 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.
