Microsoft Office Tutorials and References
In Depth Information
two tables that have identical values in matching fi elds (Figure 2–45). In this example, you
need to fi nd records in the Client table and the Recruiter table that have the same value in
the Recruiter Number fi elds.
Give me the number and
name of each client along
with the number and name
of each client’s recruiter.
Client table
Client Number
Name
...
Recruiter
Number
AC34
Alys Clinic
...
21
Recruiter table
BH72
Berls Hospital
...
24
Recruiter
Number
Last Name
First Name
...
BL12
Benton Labs
...
24
EA45
ENT Assoc.
...
27
21
Kerry
Alyssa
...
FD89
Ferb Dentistry
...
21
24
Reeves
Camden
...
FH22
Family Health
...
24
27
Fernandez
Jaime
...
MH56
Maun Hospital
...
24
34
Lee
Jan
...
PR11
Peel Radiology
...
21
TC37
Tarleton Clinic
...
27
WL56
West Labs
...
24
Join Types
The type of join that
fi nds records from both
tables that have identical
values in matching
fi elds is called an inner
join. An inner join is the
default join in Access.
Outer joins are used to
show all the records in
one table as well as the
common records; that is,
the records that share the
same value in the join
fi eld. In a left outer join,
all rows from the table
on the left are included.
In a right outer join, all
rows from the table on
the right are included.
Join of Client and Recruiter tables
Client Number
Name
...
Recruiter
Number
Last Name
First Name
...
AC34
Alys Clinic
...
21
Kerry
Alyssa
...
BH72
Berls Hospital
...
24
Reeves
Camden
...
BL12
Benton Labs
...
24
Reeves
Camden
...
EA45
ENT Assoc.
...
27
Fernandez
Jaime
...
FD89
Ferb Dentistry
...
21
Kerry
Alyssa
...
FH22
Family Health
...
24
Reeves
Camden
...
MH56
Maun Hospital
...
24
Reeves
Camden
...
PR11
Peel Radiology
...
21
Kerry
Alyssa
...
TC37
Tarleton Clinic
...
27
Fernandez
Jaime
...
WL56
West Labs
...
24
Reeves
Camden
...
Figure 2–45
The following are guidelines related to joining tables.
Plan
Ahead
Determine whether more than one table is required.
1. Determine whether more than one table is required . Examine the query or request to see
if all the fi elds involved in the request are in one table. If the fi elds are in two (or more)
tables, you need to join the tables.
2. Determine the matching fi elds . If joining is required, identify the matching fi elds in the
two tables that have identical values. Look for the same column name in the two tables
or for column names that are similar.
(continued)
Search JabSto ::




Custom Search