How to Add a Calculated Field to an MS Access Query

Microsoft Access, Microsoft Office

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.

To get more from Access, take a look at our Access training courses here.