MS Access Reports: Sums from distinct group headers

..contributing to IT in London since 2002..

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.

 

Comments: 1

  1. Davit says:

    Thanks, your article helped me a lot 🙂

Add your comment