Report Totals in MS Access

Report totals are calculated values that summarize records in a report. Totals can be displayed for the entire report or for individual groups.

Example: Student Fee Report

Total Fee Paid: ₹12,000

The report can display the total fee paid by all students instead of requiring you to add the values manually.

Aggregate functions calculate summary values from multiple records.

You can add a calculated control to a report to display a total. The expression used depends on the field and the calculation you want to perform.

Steps

1. Open your report in Design View.
2. Identify the section where the total should appear.
3. Add a text box using the Design tab.
4. Select the text box and press F4 to open the Property Sheet.
5. Set the Control Source property to the required expression.
6. Save and preview the report.

Example: Calculate Total Fees

If the numeric field is named FeePaid, use this Control Source expression:

=Sum([FeePaid])

The expression calculates the sum of FeePaid values within the applicable report or group scope.

A grand total summarizes all applicable records in the report. For example, it can show the total fees paid by every student included in the report.

Steps

1. Open the report in Design View.
2. Make sure the Report Footer section is visible.
3. Add a text box to the Report Footer.
4. Set its Control Source to the required aggregate expression.
5. Add a label such as Grand Total.
6. Save the report and check the result.

Example Expression

=Sum([FeePaid])

When placed in the Report Footer, this expression generally displays the sum for the report's records.

Group totals calculate values separately for each group in a report. For example, a course-wise fee report can display the total fees paid by students in each course.

Example

Expected Summary

Steps

1. Open the report in Design View.
2. Open the Group & Sort pane.
3. Group the report by Course.
4. Make the relevant group footer visible.
5. Add a text box to the group footer.
6. Set its Control Source to =Sum([FeePaid]).
7. Save and preview the report.

The total is calculated separately for each group when the text box is placed in the appropriate group footer.

The Count function is useful when you want to display the number of students, employees, orders, or other records.

Example Expression

=Count([StudentID])

This counts non-null StudentID values in the applicable report or group scope.

Steps

1. Open the report in Design View.
2. Add a text box to the Report Footer or relevant Group Footer.
3. Set the Control Source to =Count([StudentID]).
4. Add a suitable label, such as Total Students.
5. Save and preview the report.

You can use aggregate functions to calculate other useful summary values in a report.

Average

=Avg([Marks])

Calculates the average of the non-null Marks values in the applicable scope.

Maximum

=Max([FeePaid])

Finds the highest FeePaid value.

Minimum

=Min([FeePaid])

Finds the lowest FeePaid value.

Grouping and aggregate functions can be combined to create detailed summaries.

Example: Course-Wise Student Count

1. Create a report using student data.
2. Group records by Course.
3. Open the report in Design View.
4. Display the relevant group footer.
5. Add a text box to the group footer.
6. Set the Control Source to =Count([StudentID]).
7. Label the control Students in Course.
8. Save and preview the report.

Example Output

These values are illustrative and based on the sample records shown in this lesson.

Formatting helps users identify totals quickly. You can format total controls in the same way as other report controls.

Recommended Formatting

• Use bold text for grand totals.
• Give total labels meaningful names.
• Use suitable number or currency formatting.
• Align totals with the corresponding numeric fields.
• Keep group totals and grand totals visually distinct.

Steps

1. Open the report in Design View or Layout View.
2. Select the text box displaying the total.
3. Open the Format tab or Property Sheet.
4. Adjust font, alignment, and number formatting.
5. Save and review the report in Print Preview.

Problem 1: Total Displays #Error

Possible solution: Check the field name, Control Source expression, and report's record source.

Problem 2: Total Is Incorrect

Possible solution: Check which records are included in the report and whether the total control is placed in the correct report or group section.

Problem 3: Count Is Lower Than Expected

Possible solution: Count ignores null values in the specified field. If necessary, use a field that contains a value for every record, such as a primary key.

Problem 4: Total Is Blank

Possible solution: Verify that the report contains records and that the source field contains suitable values.

Problem 5: Group Totals Repeat Incorrectly

Possible solution: Check the grouping configuration and ensure the total control is in the intended Group Footer rather than an unrelated section.

Create a student fee report that displays individual payments, course-wise totals, and a grand total.

Required Fields

• StudentID
• StudentName
• Course
• FeePaid

Tasks

1. Create or open a table containing student fee data.
2. Create a report named rptFeeSummary.
3. Group the report by Course.
4. Display the total fee paid for each course.
5. Count the students in each course.
6. Display the grand total in the Report Footer.
7. Format all monetary values as currency.
8. Save and preview the report.

1. What are report totals in MS Access?
2. Which function calculates the sum of numeric values?
3. What is the purpose of the Count function?
4. Which function calculates an average?
5. What is the difference between group totals and grand totals?
6. Where can you place a grand total control?
7. How do you calculate total fees using a text box?
8. Why is a primary key useful when counting records?
9. How can you display totals as currency?
10. What should you check if a total displays an error?