How to reference a calculated field in access query

/en/access2013/designing-your-own-database/content/

Calculated fields and totals rows let you perform calculations with the data in your tables. Calculated fields perform calculations using data within one record, while totals rows perform a calculation on an entire field of data.

Calculated fields

When you create a calculated field, you are adding a new field in which every row contains a calculation involving other numerical fields in that row. To do this, you must enter a mathematical expression, which is made up of field names in your table and mathematical symbols. You don't need to know too much about math or expression building to create a useful calculated field. In fact, you can write robust expressions using only grade-school math. For instance, you could:

Use + to find the sum of the contents of two fields or to add a constant value (such as + 2 or + 5) to a field
Use * to multiply the contents of two fields or to multiply fields by a constant value
Use - to subtract one field from other or to subtract a constant value from a field

In our example, we will use a table containing the orders from one month. The table contains items listed by sales unit—single, half-dozen, dozen. One column lets us know the number sold of each sales unit. Another lets us know the actual numerical value of each of these units. For instance, in the top row you can see that two dozen fudge brownies have been sold and that one dozen equals 12 brownies.

To find the total number of brownies that have been sold, we'll have to multiply the number of units sold by the numerical value of that unit—here, 2*12, which equals 24. This was a simple problem, but performing that calculation for each row of the table would be tedious and time consuming. Instead, we can create a calculated field that shows the product of these two fields multiplied together on every row.

To create a calculated field:

Select the Fields tab, locate the Add & Delete group, and click the More Fields drop-down command.
Clicking the More Fields drop-down command
Hover your mouse over Calculated Field and select the desired data type. We want our calculation to be a number, so we'll select Number.
Selecting the calculated field type
Build your expression. To select fields to include in your expression, double-click the field in the Expression Categories box. Remember to include mathematical operators like the + or - signs. Because we want to multiply our two fields, we'll put the multiplication symbol (*) between them.
Building the expression for a calculated field
Click OK. The calculated field will be added to your table. If you want, you can now sort or filter it.
The calculated field shows the product of the two fields to its left

For more examples of mathematical expressions that can be used to create calculated fields, review the Arithmetic Expressions in the Expression Builder dialog box.

Arithmetic operators in the Expression Builder

Totals rows

The totals row adds up an entire column of numbers, just like in a ledger or on a receipt. The resulting sum appears in a special row at the bottom of your table.

For our example, we'll add a totals row to our calculated field. This will show us the total number of items sold.

To create a totals row:

From the Home tab, locate the Records group, then click the Totals command.
Clicking the Totals command
Scroll down to the last row of your table.
Locate the desired field for the totals row, then select the second empty cell below the last record for that field. When a drop-down arrow appears, click it.
Clicking the totals row drop-down arrow
Select the function you want to perform on the field data. In our example, we'll choose Sum to add all of the values in the calculated field.
Selecting the function to be performed on the field
The totals row will appear.
The totals row showing the total number of products sold

/en/access2013/creating-a-parameter-query/content/

by Avantix Learning Team | Updated October 14, 2022

Applies to: Microsoft® Access® 2010, 2013, 2016, 2019, 2022 and 365 (Windows)

You can create calculated fields in select queries in Microsoft Access in the QBE (query by example) grid. You'll need to learn a few syntax rules and then you can create simple to more complex calculations. Calculated fields can also be created in other types of Access queries.

Recommended article: 10 Microsoft Access Tips for Working with Select Queries

Create a calculated field in a select query

To create a select query with a calculated field (which would appear in each record in Datasheet View):

Click the Create tab in the Ribbon and then click Query Design in the Queries group.
Double-click the desired tables and then click Close.
In the grid, in a blank column in the Field row, enter the new field name followed by a colon (:). Anything after the colon is part of the mathematical expression used in the field. When you enter the name of the new field, do not use periods (.), square brackets ([]) or exclamation marks (!). Also, do not use the same name as another field in a table in your database.
After the new field name and the colon, enter the expression. When you enter field names in the expression, they should appear in square brackets such as [actualsales]. You can right-click in the field row and select Zoom or press Shift + F2 to "zoom in" to make it easier to enter the expression. For example, you could enter Variance:[actualsales]-[projectedsales] in the field row or Zoom dialog box.
In the expression, enter any operators or functions as appropriate (+ for addition, – for subtraction, / for divide and * for multiply). You can also enter Access functions.
If you are in the Zoom dialog box, click OK.
Press Enter.
Right-click the tab for the query and select Datasheet View. You can also click Run in the Results group in the Query Tools Design tab in the Ribbon.

In order for these calculations to work, the fields that you include in the expression should be fields in the displayed tables. Also, watch out for typing errors. Actual Sales is different from ActualSales and square brackets, not round brackets, are used to enclose the fields.

Below is the Zoom dialog box:

A calculated field in Design View in a query may appear in the Field row as follows:

Variance:[ActualSales]-[ProjectedSales]

These calculations are not case sensitive so you could also enter:

Variance:[actualsales]-[projectedsales]

Create a calculated field using the Expression Builder

To create a query with a calculated field using the Expression Builder:

In Query Design View, in the grid, click in a blank column in the Field row and then enter the new field name followed by a colon (:).
Click Builder in the Query Setup group in the Query Tools Design tab in the Ribbon or press Ctrl + F2. The Expression Builder appears. Enter the expression or click the + beside Functions and then click Built-In Functions to view the functions available in Access.
Continue entering the desired formula.
Click OK.
Press Enter.
Right-click the tab for the query and select Datasheet View. You can also click Run in the Results Area in the Query Tools Design tab in the Ribbon.

Below is the Expression Builder:

You can create all kinds of basic and more complex calculations using the functions in Microsoft Access not only in queries, but in forms, reports and other objects. We'll be showing some of the other calculations you can create in future articles.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, JOIN our email list.

More resources

Key Features and Benefits of Microsoft Access

How to Filter a Report on the Fly in Microsoft Access

10 Tips for Creating Select Queries in Microsoft Access

10 Techniques for Designing Forms in Microsoft Access

How to Convert a Microsoft Access Report to PDF (3 Ways)

Related courses

Microsoft Access: Introduction

Microsoft Access: Intermediate / Advanced

Microsoft Access: Introduction to VBA (Visual Basic for Applications)

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at