Calculating in a report in Microsoft Access 2019

Records can be calculated in a report by grouping values based onspecific fields. Calculated fields can be included in a report. You canwork out the total sum of the amount received, the maximum and minimumvalues, the averages, or the count of a number of items. The calculationthat is performed determines where the calculated field will bepositioned in the report.

Here are some examples:

=sum([amt_received])

Calculates the sum of the received payments from parents

=average([salary])

Calculates the average salary

=maximum([salary])

Displays the highest salary

=minimum([salary])

Displays the lowest salary

=count([surname])

Counts the number of students

=[salary]*12

Multiplies the value by 12

=[amt_received]/12

Divides the amount received by 12

=[salary]+[bonus]

Adds the salary to the bonus

=[salary]-[bonus]

Subtracts the salary from the bonus

When using the preceding calculations, note the following:

  • Brackets are extremely important, just as they are when used inExcel to construct formulas. Just like in Excel, we use square bracketsto identify field names on a report.
  • Field names are case sensitive.
  • Field names must be typed exactly as they appear in the originalfield.
  • Uppercase, lowercase, and spaces must correspond.

Let’s go through an example of calculating a report together:

  1. Open a report to edit in Designview. We will use the StudentsRPT report created inthe previous example.
  2. Create a textbox in the report’s footer:
ms office 728
  1. Once the Text Box control has beeninserted onto the report, the two textboxes may appear to overlap:
ms office 87
  1. Separate the Text Boxcontrol by dragging the unbound box away from the label. Change the nameof the Text label to Number ofPayments. Resize if necessary:
ms office 683
  1. Click on the Unbound control andtype =count(*). Change the font, if necessary, using theFormat tab on the top ribbon.
  2. Preview your report to check the changes. Then, change back to Design view.
  3. Edit your report’s footer to containa =sum([AMT_RECEIVED]) function to calculate the total amountof fee payments received per student:

ms office 648

  1. Change the view to Report View.Note that the calculations are visible at the very end of the report inthe footer section:
ms office 180

In the next section, we will look at formatting values to display thecurrency format on a report.

Leave a Comment