Subtotals in report (1 Viewer)

[tmyers]

I am reading around trying to figure this one out, but as always I like asking here as well.

In my report I have been working on, there are times where I will need it to have subtotals to segregate pricing. How can I do that in a way that allows the user to break the data in the report into various subtotals, or leave it alone to just have one grand total?

 

[theDBguy]

The simplest approach I could think of is to create two separate reports: one with subtotals and one without.

 

[tmyers]

The simplest approach I could think of is to create two separate reports: one with subtotals and one without.

I thought about something like that, but wasn't sure if Access had something a little more "seamless".

 

[theDBguy]

I thought about something like that, but wasn't sure if Access had something a little more "seamless".

"Seemless" would probably mean using code and could get complex. Letting the user filter the data is easy. Letting them control the layout is not so easy.

 

[Pat Hartman]

We don't really know what business problem you are trying to solve. What is the difference in the the data that causes subtotals vs no subtotals?

 

[tmyers]

We don't really know what business problem you are trying to solve. What is the difference in the the data that causes subtotals vs no subtotals?

Sometimes a construction job requires us to have our quote broken down to various levels. Such as per floor, per building etc. So ideally, rather then have multiple reports, I would have a report that could do subtotals. So subtotal for building 1, subtotal for building 2 etc then still have the grand total at the bottom.

The more I read on it, I don't think it is possible. I could possibly do a second report that is filled with sub-reports, but that seems messy. This is something I may just not be able to easily reconcile.

 

[Pat Hartman]

You can do this by manipulating the grouping. Make a group field in your query and either leave it null or fill it with the values that you want to group on. Add grouping to the report on the group field. So when you run the report in one case, the group field is blank and so you only see ONE group total at the end. When the group field contains the building name or whatever the group is, you will have sub totals for each group.
'
You will need code to control the grand totals though since you won't want them to print when the group is null. That would just show the same totals twice in a row.