Microsoft Office Tutorials and References
In Depth Information
(continued)
3. Determine whether sorting is required . Queries that join tables often are used as the
basis for a report. If this is the case, it may be necessary to sort the results. For example,
the Recruiter-Client Report is based on a query that joins the Recruiter and Client tables.
The query is sorted by recruiter number and client number.
4. Determine restrictions . Examine the query or request to see if there are any special
restrictions. For example, the query may only want clients whose current due amount is $0.00.
5. Determine join properties . Examine the query or request to see if you only want records
from both tables that have identical values in matching fi elds. If you want to see records
in one of the tables that do not have identical values, then you need to change the join
properties. When two tables have fi elds with the same name, you also need to determine
which table contains the fi eld to be used in the query. For example, if you want to see
all recruiters, even if they have no clients, then you should include the recruiter number
from the Recruiter table in the design grid. If you want only records with identical values
in matching fi elds, then it does not matter which matching fi eld you select.
Plan
Ahead
To Join Tables
If you have determined in the design process that you need to join tables, you will fi rst bring fi eld lists for both
tables to the upper pane of the Query window. Access will draw a line, called a join line , between matching fi elds
in the two tables indicating that the tables are related. You then can select fi elds from either table. Access joins the
tables automatically.
The fi rst step is to create a new query and add the Recruiter table to the query. Then, add the Client table to
the query. A join line will appear connecting the Recruiter Number fi elds in the two fi eld lists. This join line indicates
how the tables are related; that is, linked through these matching fi elds. (If you fail to give the matching fi elds the same
name, Access will not insert the line. You can insert it manually, however, by clicking one of the two matching fi elds
and dragging the mouse pointer to the other matching fi eld.)
The following steps create a new query, add the Client table, and then select the appropriate fi elds.
1
Click Create on the
Ribbon to display the
Create tab.
fi eld list for
Recruiter table
Click the Query
Design button to
create a new query.
Click the Recruiter
table in the Show
Table dialog box to
select the table.
Show Table
dialog box
Client table
Click the Add button
to add a fi eld list for
the Recruiter table
to the query
(Figure 2–46).
Recruiter
table selected
Close
button
Add button
Figure 2–46
Search JabSto ::




Custom Search