How to Calculate Fields in an MS Access Report

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.

...
Microsoft Access Report in Design View

Step

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

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

Step

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

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

Step

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.

Step

Double-click the "Sum" function.

Step

In the expression window you will see "Sum (<<expr>>)." This is telling the report to add all values from a certain data source.

Step

Highlight "<<expr>>" 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

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

Step

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

Run the report to see your results.