ata 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 field.
For many examples of query criteria for various data types, see the article Examples of query criteria.
Specify any alternate criteria in the Or row, below the Criteria row.
If you specify alternate criteria, a field value can meet any of the listed criteria and be included in the query result.
Multiple field criteria
You can use criteria with multiple fields. When you do, all the criteria in a given Criteria or Or row must be true for the record to be included.
Specify criteria by using a field that you don't want to output
You can add a field to your query design and not include the field's data in the query output. You do this if you want to use the field's values to limit the query results, but don't want to see the field values.
Add the field to the design grid.
Clear the check box in the Show row for the field.
Specify criteria as you would for an output field.
Step 5: Summarize data
This step is optional.
You might want to summarize data, especially if your data is numeric. For example, you might want to see the average price, or total sales.
To summarize data in a query, you use the Total row. By default, the Total row is not displayed in Design view.
With the query open in Design view, on the Design tab, in the Show/Hide group, click Totals.
Access displays the Total row in the query design grid.
For each field that you want to summarize, choose the function to use from the list in the Total row. The functions that are available depend on the data type of the field.
To learn more about the Total row functionality in queries, see the article Sum or count values on a datasheet with a Total row.
Step 6: View the results
To see the query results, on the Design tab, click Run. Access displays the results of your query in Datasheet view.
To make further changes to the query, click Home > View > Design View to switch back to Design view.
Change your fields, expressions, or criteria and rerun the query until it returns the data that you want.