How to Calculate Fields in an MS Access Report

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
Microsoft Access Report in Design View

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.

Advertisement

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.

Video of the Day

Step 2

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

Advertisement

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.

Advertisement

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.

Advertisement

Double-click the "Sum" function.

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

Advertisement

Advertisement

Step 6

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

Advertisement

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.

Video of the Day

Advertisement

Advertisement

resources