Reports in Microsoft Access

Calculated Controls

Calculated fields in a report show percentages and display currency. These fields can display the value of each detail line as a percentage of the report total. You can apply currency formatting to financial fields. It is useful when you want to show formatting relevant to your report.

For example, in a financial report for an organisation, sales figures can be represented by percentages of the annual revenue. Currency formatting can be applied to display sales totals in a chosen currency.

Database files for this post can be downloaded from www.digidiscover.com/downloads

Click on the Advanced Database book cover to download the work files

1. Open the Digital Skills Courses database

2. Open the Courses report

3. View the report in Design View

4. On the Report Design tab, click on Group & Sort

5. At the bottom of the window, notice that the form is grouped by College and sorted by Course

6. Choose to group by Location

7. Then Sort by College

8. Then Sort by Price from Largest to Smallest

9. View the form in Form View

10. Enlarge the Report Footer

11. In the Controls group, click on Text Box

12. Create the Text Box beneath the Page Number text box

13. Type in the following calculation:

=sum([Price])

14. Change the label to read Total Price

15. On the Property Sheet pane, change the format of the Total Price text box to Euro

16. Change the number of decimal places to One for the Total Price text box

17. Create another textbox beneath the Total Price text box

18. Type in the following calculation:

=count([Course])

19. Give the text box a label of No. of Courses

20. Align the Total Price and Report View text boxes

21. Return to Report View

22. Notice the calculations in the Report Footer

23. Return to Design View and create another Text Box beneath the No. of Courses calculation

24. Type in the following calculation:

=Avg([Price])

25. It will find the average price of courses provided

26. Apply a Euro currency to the average course price with No Decimal Places

27. Label the text box Average Course Price

28. Return to Report View

29. Notice the calculations at the bottom of the report

30. Save the report and close it