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.

SELECT h.*, d.item, d.price, d.quantity, d.detail_subtotal, d.detail_vat, d.detail_total
FROM t_trans AS h
INNER JOIN t_trans_detail AS d 
ON h.trans_id = d.trans_id;

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.

Option Compare Database
Option Explicit

Dim myTrans As New Collection
Dim mySums(1 To 2) As Double
'1 sum of total cost
'2 sum of grandtotal
'distinct transactions

Private Sub myCalculation(lTransID As Long, myCost As Currency, myTotal As Currency)
On Error Resume Next
    myTrans.Add lTransID, "T" & lTransID
    'try to insert transaction ID with collection Key
    If Err Then 'exists
        Err.Clear
    Else 'first time, so consider distinct headers
        mySums(1) = mySums(1) + myCost
        mySums(2) = mySums(2) + myTotal
    End If
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Debug.Print "Trans ID: " & Nz(Me.trans_id, 0)
    'you will see this subroutine is called more than once
    Call myCalculation(Me.trans_id, Nz(Me.txt_totalcost, 0), Nz(Me.txt_grandtotal, 0))
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
    Me.txt_sum_totalcost = mySums(1)
    Me.txt_sum_grandtotal = mySums(2)
End Sub
Tags: , ,
Categories: