Creating Calculated Fields in Microsoft Access

Microsoft Access, Microsoft Office

When setting up reports in Access, we’re trying to learn something which wasn’t in the original data. Something calculated, derived, or otherwise figured out from the existing information. It’s fairly easy to do, though there are a few steps.

After determining the requirement—a sales tax or a shipping fee, and what calculation we need to find it—we create the field for the calculation.

With the report open in Design view, clicking the Design tab under Report Design Tools gives us the Controls group. There, we click the Text Box tool and click or drag where we want the text box (which shows field data). This normally creates a label too, which one can use or delete as desired.

Once the text box is in place, we leave it selected, open the Property Sheet, and click the Data tab. One of the Data properties is Control Source, where we can tell the text box how to get its data. On the right of this property, we bring up the Expression Builder with the Build button […].

In the Expression Builder itself, we can either double-click the fields we need for data or type in what we want manually. We also add the other items (operators, additional figures, and so on) to perform the calculations—essentially, we build a formula much like those in Excel. (Don’t need the equals sign at the front, though.) This makes it a calculated field.

Once we OK out of the box, we can fine-tune the position and size of the text box. We can move the label, or create one of our own. The users of the report will then know what it’s showing. (This is more important than some people think—in a business report it’s very important not to make assumptions about who’s going to see it, and no guesswork should be necessary to understand it.)

The term “calculated field” seems kind of unofficial—one usually sees it called an “unbound field”. It isn’t directly connected to any of the actual database data. But we often create such fields to derive info from other info. And it’s often mathematical, so saying calculated is at least logical.

To learn more, have a look at our Microsoft Access classes.