MS Access Reports: Sums from distinct group headers

The problem:
When we use formulas on textboxes in the report footers of MS Access reports, such as “=sum(a_field_in_header)”, we get incorrect results because of duplication of same amounts for multiple items in detail section.

Here is an example to make it clear:

Sample table header and detail with data

We have two tables one for header and another detail records, for example, invoice header and a item details.

So, that we get:
query

If we design a report to show the transactions:

We will get a listing of grouped transactions like this:

The report footer shows the problem and solution includes some VBA code below to do the correct calculations.

1 thought on “MS Access Reports: Sums from distinct group headers

Leave a Comment