Add a calculated field to a table

Applies to: Access for Office 365, Access 2019, Access 2016, Access 2013, Access 2010.

In Access, you can design table fields that calculate values. The calculations can contain values from fields in the same table as well as built-in Access functions.

Notes: 

  • The calculation cannot include fields from other tables or queries.

  • The results of the calculation are read-only.

  • Calculated fields are only available in Access databases using the .accdb file format.

  • You must be using Access 2010 or higher.

Create a calculated field in table datasheet view

  1. Access the table by double-clicking it in the Navigation Pane.

  1. Scroll horizontally to the rightmost column in the table, and choose the Click to Add column heading.

  1. In the list that emerges, select Calculated Field, and then choose the data type that you want for the result. Access displays the Expression Builder.

  1. Start typing the calculation that you want for this field, for example:[Quantity] * [Unit Price]

Note:  It is not necessary to precede the expression with an equal sign (=).

  1. Press OK. Access inserts the calculated field, and then highlights the field header so that you can type a field name.

  1. State a name for the calculated field, and then press ENTER.

For more information about creating expressions, see the article Build an expression.

Modify a calculated field in table datasheet view

  1. Enter the table by double-clicking it in the Navigation Pane.

  1. Select on the calculated field that you want to modify.

  1. On the Fields tab, in the Properties group, pick Modify Expression. Access displays the Expression Builder.

  1. In the expression box, set the necessary modifications to the calculation.

Note:  It is not necessary to precede the expression with an equal sign (=).

  1. Press OK.

Top of Page

Leave a Reply

%d bloggers like this: