Microsoft Office Tutorials and References
In Depth Information
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 ﬁ 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 ﬁ elds with the same name, you also need to determine
which table contains the ﬁ 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 ﬁ elds, then it does not matter which matching ﬁ eld you select.
To Join Tables
If you have determined in the design process that you need to join tables, you will ﬁ rst bring ﬁ eld lists for both
tables to the upper pane of the Query window. Access will draw a line, called a join line , between matching ﬁ elds
in the two tables indicating that the tables are related. You then can select ﬁ elds from either table. Access joins the
The ﬁ 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 ﬁ elds in the two ﬁ eld lists. This join line indicates
how the tables are related; that is, linked through these matching ﬁ elds. (If you fail to give the matching ﬁ elds the same
name, Access will not insert the line. You can insert it manually, however, by clicking one of the two matching ﬁ elds
and dragging the mouse pointer to the other matching ﬁ eld.)
The following steps create a new query, add the Client table, and then select the appropriate ﬁ elds.
• Click Create on the
Ribbon to display the
ﬁ eld list for
• Click the Query
Design button to
create a new query.
• Click the Recruiter
table in the Show
Table dialog box to
select the table.
• Click the Add button
to add a ﬁ eld list for
the Recruiter table
to the query