Database Reference
In-Depth Information
Within groups of
customers of a sales rep,
records are sorted second
by CustomerName
51
RepNum ield
is displayed in the
correct position
Records are
sorted irst
by RepNum
FIGURE 2-28
Query results
JOINING TABLES
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.
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.
You cannot create this query using a single table
—
