Databases Reference
In-Depth Information
Within groups of
customer of a sales rep,
records are sorted second
by CustomerName
49
RepNum field is
displayed in the
correct location
R
e
pNum sorted
first by RepNum
FIGURE 2-28
Query results
So far, the queries used in the examples have displayed records from a single table. In many cases, you'll need
to create queries to select data from more than one table. To do so, it is necessary to
join
the tables based
on matching fields in corresponding columns. To join tables in Access, first you add the field lists for both tables
to the upper pane of the Query window. Access will draw a line, called a
join line
, between matching fields in
the two tables, indicating that the tables are related. (If the corresponding fields have the same field name
and at least one of the fields is the primary key of the table that contains it, Access will join the tables
automatically.) Then you can select fields from either or both tables, as you will see in the next example.
EXAMPLE 13
List each customer's number and name, along with the number, last name, and first name of each customer's
sales rep.
You cannot create this query using a single table—the customer name is in the Customer table and the
sales rep name is in the Rep table. The sales rep number can come from either table because it is the match-
ing field. To select the correct data, you need to join the tables by adding both the Customer and Rep table
field lists to the upper pane and then adding the desired fields from the field lists to the design grid, as shown
in Figure 2-29.
