ms access sum on groups, not on details

Question

I am trying to calculate a SUM in an MS Access report the following way:

• Group1header - label

  • Group2header - value
    • Detail - example
    • Detail - example
  • Group2header - value
    • Detail - example

• Group1footer [sum of Group2header value]

Somehow, when more detail rows appear, the sum in group1footer is incorrectly calculated (adds value for each detail).

I can not calculate the sums in the query, because the "value" is already a calculated in the query (a subquery would return to many rows):

(
    (
        (sl_ticketdetail.weight,0) * sl_ticketdetail.amount 
        - (
            SELECT SUM(sl_invoicedetail.amount)
            FROM sl_invoicedetail 
            WHERE ticketdetailid = sl_ticketdetail.ticketdetailid
        )
        / 1000
    )
    * sl_ticketdetail.cost
)
/ 1000

Thanks for your reactions.

Answer 1

Hi,

Are you saying your are getting results like this:

Group 1a
    Group 2a
        Foo1         1
        Foo2         1
        foo3         2
    Group 2a Sum   4
    Group 2b
        Foo1         3
        Foo2         3
    Group 2a Sum   6
Group 1a Sum    10
Group 1b
    Group 2a
        Foo1         4
        Foo2         1
        foo3         2
    Group 2a Sum   7
    Group 2b
        Foo1         4
        Foo2         3
    Group 2a Sum   14
Group 1b Sum    21

This is the behaviour I would expect. I was able to do it by putting =Sum([value]) in an unbound field in each group footer (and even in the report footer).

I know 'works for me' isn't very helpful.

Have you labelled the detail's values fields (or the summary fields) with the same name as the data source? Sometime MS Access has weird behaviour if your fields have the same name as their bound data source (I tend to rename them slightly so I'm sure what I'm referring to in code).

Answer 2

Since you already have the Group2 sums pre-calculated in your query they will be repeated for each row of results and therefore cannot be used (as you found out) to calculate the Group 1 totals.

You have two solutions

1) pre-calculate the Group1 totals in your query as well and simply report them liek you do the Group2 totals

2) use code in the Group2 footer format/print events to capture the value and manually increment a running Group1 total

I would say 1) is the easiest - 2) is a little hairy and sometimes results in inaccurate totals if the users pages back and forth

Answer 3

You would have to have the record source of the main report to include the totals for Group 2. Then you would need a sub report with a different record source that is on the detail level.

I think your best bet, is to omit the totals in the query and just let the report do the totals on the details. Later, the user may want totals on the same date but a different grouping (yes, you could create another record source). This should also address if a user applies a filter on the report (You may or may not have given them this option.) on a field other than the grouping.