Simple Report With SubTotals and Grand Totals

Please consider supporting this site by purchasing items from the Book Store. Thanks for visiting!

Someone read the post from 2 days ago and took this to a slightly further point.

How can I list all the details, and also list subtotals by a subset of the itemID, and then a grand total. They were referring specifically to a report of the data.

Here is some fake data I created to illustrate.

itemid   cost
01142    $1.00
01222    $7.00
01333    $2.00
01623    $4.00
01777    $7.00
01997    $4.00
02456    $8.00
02556    $5.00
02666    $9.00
02716    $8.00
02796    $9.00
02916    $2.00
03455    $5.00
03485    $4.00
03555    $7.00
03675    $7.00
05666    $6.00
05886    $1.00

The report should look like this. (plus appropriate headers)

itemid   cost
01142    $1.00
01222    $7.00
01333    $2.00
01623    $4.00
01777    $7.00
01997    $4.00
01 subtotal = $25.00
02456    $8.00
02556    $5.00
02666    $9.00
02716    $8.00
02796    $9.00
02916    $2.00
02 subtotal = $41.00
03455    $5.00
03485    $4.00
03555    $7.00
03675    $7.00
03 subtotal = $23.00
05666    $6.00
05886    $1.00
05 subtotal = $7.00

grand total = $96.00

Here are the steps needed in Microsoft Access 2000. I am not putting in every small detail, as I believe you should think about what you are doing, and copying every tiny detail from me will not help you.

• start the report wizard, giving it the name of the table you are needing
• select the itemID and cost fields
• choose itemID as a grouping field
• click the Grouping Options button and choose a Grouping interval of “2 initial letters”
• no sorting is needed, but under Summary Options you want the Sum of the cost field
• pick a style and finish the report’s creation (Note: give it a proper name, like rptCosts, or similar)

The report is not quite what we wanted, but is close.

Next I went into the design view of the wizard created report and …

• deleted the “Summary for …” item from the itemID footer
• moved the “Left$ …” item from the itemID header to the itemID footer
• rearranged the objects in the itemID footer to better emulate the design I wanted
• went into the Sorting and Grouping section under the view menu
• changed the itemID Group Header to “No”
• continued to play with formatting until I had it the way I wanted it

See printouts, before and after changes, by clicking on the links in this sentence.

The key in my MS Access self-done education period was to use the wizard like we just did, and look at the design and especially properties of objects, and the Sorting and Grouping dialogue box. I experimented and discovered “stuff”.

Hopefully I can help you discover that “stuff” faster than I did, but in a way that still helps you “see” what is going on. Don’t ever depend solely on a recipe book. Discover the principles so that you can create your own recipes.