In Microsoft Access, building reports can help the user to determine the results of data which has been filtered through queries and stored in tables. People who are efficient in using Access find that data can not only be displayed but can also be calculated through a series of functions built into the MS Access Library. By mastering these functions and the report’s functions, any mediocre report can be turned into a professional piece of art.
Instructions to calculate fields in an MS Access Report
Fields can be calculated by following the step by step procedure as mentioned below.
Step 1: Go to the Microsoft Access application and open the field where you want to add the calculating field. It needs to be ensured that the particular table is already created, along with the query for the report to read from, as the question is about reports.
Step 2: Go to ‘Design View’. In the ‘Page Footer’ section of the report, add a text box.
Step 3: The textbox needs to be right clicked and not the label associated with it. Select the option that says ‘Properties’. On the right side of the window, the ‘Property Sheet’ will open. On the ‘Property Sheet’, look under the ‘All’ section for and select the option which says ‘Control Source’. Then click on the ‘…’ tab. This would result in opening of the Expression Builder.
Step 4: The ‘Functions’ folder in the right hand column of the window needs to be double clicked on. This would open the ‘Functions’ tree. After that, select the folder that says ‘Built-in Functions’.
Step 5: From the list on the extreme right corner, select the function that you wish to perform. The middle column then breaks down the different functions into separate categories.
The ‘Sum’ function needs to be double clicked on.
Then you will see ‘Sum (<<expr>>)’ in the expression window. This means that it tells the report to add all the values from a particular data source.
Step 6: You need to highlight ‘<<expr>>’ and hit the ‘Delete’ key. To open the Query tree, double click on the ‘Queries’ folder. This will result in the display of all your project queries. Once you click on the query folder, a list of all available queries would appear in the centre column. Double click on the query that you are using for your data source. The name of the query would be ‘WeeklyCosts’.
Step 7: The expression window would now read as ‘Sum (WeeklyCosts)’
Step 8: To close the window, click on the ‘OK’ button on the ‘Expression Builder’. In the Control Source of the Property Sheet, the formula Sum (WeeklyCosts) would be displayed.
Step 9: You can then run the report to see the results.
You can just type in the data into the Control source and not go through the Expression Builder if you are familiar with the functions. Using the Expression Builder is a good idea for those who are not very familiar with the application. The above steps are mostly for those who are proficient and experienced Access users.
