totn Access

MS Access 2003: Display a subtotal for each category within a report

This MSAccess tutorial explains how to display a subtotal for each category within a report in Access 2003 (screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, I'm trying to create a report that will display total hours at the end of each category. For instance, in my database I have the following fields titled: "computer" and "total hours". The values in "total hours" represent the number of hours that a technician has worked on a computer. I would like a total of hours for computer #1 displayed before the detail for computer #2 is displayed.

Answer: First, open your report in Design view. Then click on the "Sorting and Grouping" button in the toolbar.

Microsoft Access

When the Sorting and Grouping window appears, select computer as the Field/Expression and Ascending as the Sort Order.

Then in the Group Properties section at the bottom portion of the window, set the Group Footer property to "Yes".

Microsoft Access

Now when you return to your report, you should see a section called "computer Footer". Create a new text box in the "computer Footer" section.

Microsoft Access

Then under the View menu, select Properties. When the Properties window appears for your new text box, set the Control Source property to the following:

=Sum([total hours])

Microsoft Access

We've also chosen to bold the text box by setting the Font Weight property to Bold.

Now when you return to your report, it should look as follows:

Microsoft Access

If you Preview your report, you should now see a subtotal after each computer is listed.

Microsoft Access