How do you create a calculated field in Access query?

Calculated fields are a really big help in Access; they let us derive data from existing information. And they’re flexible, too. If one of the source items changes, the calculation updates immediately, just like an Excel formula. But some people aren’t aware you can add them to a query. And this is an especially cool thing, because every time we run a query, we get the latest info.

One example where we might use this capability would be calculating a restock—how many items we need to add. We can open the database, check the table we draw data from, and design a query.

After getting the fields into the query grid, we can right-click in the next free column, bring up the Expression Builder, and write the formula. We can use any fields we like, and have them perform whatever necessary calculation. We can also, equally importantly, label the expression we built. If the column has a generic label, it won’t tell the user what it’s for, at least not as well.

We can even use the first calculated field in the query to help out a second one! This is yet another ability that even some experienced users aren’t aware of, or don’t use to its fullest. Take the number of items we need to stock up on each row and multiply it by the unit cost. (This we can do even before coffee. ? ) Just like that, we get our cost.

“Chaining” these calculated fields, deriving data from other derived data to help the query, is quite simple. It’s just that some people seem to think you can’t go more than one “generation” from the original data in this process. Not true. It is important to build one formula at a time, and make sure each one works before building the next. But it’s just as straightforward as adding two plus two and multiplying the result by six.

Did you know that you could calculate fields in the query in Microsoft Access? If the user wants a calculated field in a query, the user needs to input a name for the calculated field, a colon, and the calculations in one of the columns in the Field row of the query’s design grid.

A calculated field is an individual record calculation because each calculation only involves fields in a specific record.

Create Calculated Fields in Access

Open Microsoft Access.

Create a table or an existing table.

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:

1. Select the Fields tab, locate the Add & Delete group, and click the More Fields drop-down command.
   
   Clicking the More Fields drop-down command

2. 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

3. 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

4. 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.