How to Calculate Fields in an MS Access Report

By Launie Sorrels

Building reports in Microsoft Access can aid the user in determining the results of data that has been filtered through queries and/or stored in tables. As an Access user becomes more proficient at utilizing Access reports, she finds that the data can not only be displayed, but calculated through a series of functions that are built into the Microsoft Access library. Mastering these functions and the report's functions can turn any mediocre report into a professional piece of art.

Step 1

Open Microsoft Access. Open the report in which you want to add the calculating field. Since the question is about reports, the user should have already created a table and a query for the report to read from.

Step 2

Go in to "Design View." Add a text box in the "Page Footer" section of the report.

Step 3

Right-click the textbox (not the label associated with the textbox), and choose the "Properties" option. The "Property Sheet" will open on the right side of the window. Select the "Control Source" option located in the "All" section on the "Property Sheet." Click the "..." button. This will cause the Expression Builder to open.

Step 4

Double-click the "Functions" folder in the right-hand column. The "Functions" tree will open. Select the "Built-In Functions" folder.

Step 5

Select the function you wish to perform from the list on the far right side. The middle column breaks down the various functions into separate categories. Double-click the "Sum" function. In the expression window you will see "Sum (<>)." This is telling the report to add all values from a certain data source.

Step 6

Highlight "<>" and press the "Delete" key. Double-click the "Queries" folder to open the Query tree, which will display all of your project queries. Single-click the query folder and a list of available queries will appear in the center column. Double-click the query you are using for your data source. The query will be called "WeeklyCosts."

Step 7

The expression window now reads "Sum (WeeklyCosts)."

Step 8

Click the "OK" button on the "Expression Builder" to close the window. The formula, Sum (WeeklyCosts), is displayed in the Control Source of the Property Sheet.

Step 9

Run the report to see your results.

Tips & Warnings

  • If you know the functions you want to call you can just type in the data into the Control Source and not go through the Expression Builder. Until you are better familiar with Access, it is a good idea to utilize the Expression Builder.
  • This article is written for experienced Access users.