ed fields. For example, say you have an Employees table in which the ReportsTo field for each employee's record displays his or her manager's ID instead of name. You could use a self-join to display the manager's name in each employee's record instead.
When you add a data source a second time, Access appends _1 to the name of the second instance. For example, if you added the Employees table twice, the second instance would be named Employees_1.
Step 2: Join related data sources
If the data sources that you add to a query already have relationships, Access automatically creates an inner join for each relationship. If referential integrity is enforced, Access also displays a "1" above the join line to show which table is on the "one" side of a one-to-many relationship and an infinity symbol (∞) to show which table is on the "many" side.
If you add queries to your query, and have not created relationships between those queries, Access does not automatically create joins between those queries, or between queries and tables that are not related. If Access does not create joins when you add data sources, you should usually add them yourself. Data sources that are not joined to any other data source can cause problems with the query results.
You might also want to change the type of a join from an inner join to an outer join, so that your query includes more records.
Add a join
To add a join, drag a field from one data source to a corresponding field on another data source.
Access displays a line between the two fields to show that a join has been created.
Change a join
Double-click the join you want to change.
The Join Properties dialog box appears.
In the Join Properties dialog box, review the three options.
Click the option that you want to use, and then click OK.
After the joins are ready, you add output fields — fields that have data that you want in the query results.
Step 3: Add output fields
You can easily add a field from any of the data sources that you added in step 1.
To add a field, drag the field from a data source in the upper pane of the query design window down to the Field row of the design grid, in the bottom pane of the query design window.
When you add a field this way, Access automatically fills in the Table row of the design grid to reflect the data source of the field.
Tip: If you want to quickly add all fields down to the Field row of the query design grid, double-click the table or query name from the upper pane to highlight all the fields in that source and then drag them all down to the design grid at the same time.
Use an expression as an output field
If you want to perform calculations or use a function to produce query output, you can use an expression as an output field. An expression can use data from any of the query data sources, as well as functions, such as Format or InStr, and can also contains constants and arithmetic operators.
In an empty column of the query design grid, right-click the Field row, and then click Zoom on the shortcut menu.
In the Zoom box, type or paste your expression. Preface your expression with the name you would like to use for the expression output, followed by a colon. For example, if you wanted the expression to be labeled "Last updated", you would start your expression with Last updated:.
Note: You can do a wide variety of things by using expressions. A thorough explanation of expressions is beyond the scope of this article. For more information about creating an expression, see the article Build an expression.
Step 4: Specify criteria
This step is optional.
You use criteria to limit the records that your query returns, on the basis of whether field values meet the criteria that you specify.
Specify criteria for an output field
In the query design grid, in the Criteria row of the field that has values that you want to limit, type an expression that field values must satisfy to be included in your results. For example, if you wanted to limit a query so that only records where the value of the field City is Las Vegas, type Las Vegas in the Criteria row under that 