ASCII table

Calculating Fields in a Query

Create a field that performs custom calculations or manipulates field values in a query

You can create a new field that displays the results of a calculation you define with an expression.

1 Open the query in Design view.

2 Do one of the following:

Type an expression in an empty cell in the Field row. If the expression includes a field name, you must place brackets around the name.

If you need help creating the expression, use the Expression Builder. To display it, right-click in the Field cell where you're adding the calculated field, and then click Build.

1 In the lower-left box of the Expression Builder, double-click or click the folder containing the element you want.

2 In the lower-middle folder, double-click an element to paste it into the expression box, or click a category of elements.

3 If you select a category in the lower-middle box, values display in the lower-right box. Double-click a value to paste it in the expression box.

4 Paste any operators you want in the expression by placing the insertion point in the expression box where you want the operator, and clicking one of the operator buttons that are in the middle of the builder.

5 When your expression is complete, click OK.

Microsoft Access copies your expression to the location where you started the Expression Builder. If that location already contains a value, your new expression replaces the value or text.

After you press ENTER or move to another cell, Microsoft Access enters the default field name ExprN, where N is an integer incremented for each new expression field in the query. The name appears before the expression and is followed by a colon. In a datasheet, this name is the column heading.

You can select ExprN and type a more descriptive name, such as NewPrice.

3 If the expression includes one or more aggregate functions (Sum, Avg, Count, Min, Max, StDev, or Var), and if the design grid includes another field or fields that you want to use for grouping, click Totals  on the toolbar (unless the Total row is already displayed). Leave Group By in the Total cell for the grouping field, and in the calculated field, change Group By to Expression.

4 If you want, do any of the following to complete the query:

Enter criteria to affect the calculation.

Sort the results.

Set field properties such as Format (since the field doesn't inherit properties from the underlying table).

Assignment