Performing Calculations in Queries

In addition to the built-in functions you can use to compare values in a query, you can use the Expression Builder to create your own calculations using arithmetic operators. By clicking the operator buttons you want to use and entering constant values as needed, you can use the Expression Builder to include expressions in a query. For example, to determine fees based on a contract amount, you can create an arithmetic expression in your query to compute the results. When you run the query, Access performs the required calculations and displays the results. You can also insert functions, such as AVG and Count, to perform other operations. When you insert a function, <<expr>> appears in parentheses, which represents an expression. Select <<expr>> and replace it with a field name , which you can select in Expression Builder.

Create a Calculated Field

Within Query Design view, position the insertion point in the Field row of a blank column in the design grid.

Click the Build button on the Query Design toolbar.

Double-click the field (or fields) you want to use in the calculation.

Build an expression using the operator buttons and elements area.

• Click the button corresponding to the calculation you want.

• Click the Operators folder, click the Arithmetic folder, and then click the operator you want to use.

• Click the Functions folder, click Built-In Functions, and then click the function you want to use.

Type any other values (constants) you want to include in the expression.

Click OK.

Click the Run button on the Query Design toolbar.