{"id":59,"date":"2009-01-11T22:53:06","date_gmt":"2009-01-11T21:53:06","guid":{"rendered":"http:\/\/muratyaman.co.uk\/wp\/?p=59"},"modified":"2020-04-01T12:57:38","modified_gmt":"2020-04-01T11:57:38","slug":"ms-access-reports-sums-from-distinct-group-headers","status":"publish","type":"post","link":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/2009\/01\/ms-access-reports-sums-from-distinct-group-headers\/","title":{"rendered":"MS Access Reports: Sums from distinct group headers"},"content":{"rendered":"

The problem:<\/strong>
\nWhen we use formulas on textboxes in the report footers of MS Access reports, such as “=sum(a_field_in_header)”<\/b>, we get incorrect results because of duplication of same amounts for multiple items in detail section.<\/p>\n

Here is an example to make it clear:<\/p>\n

\"Sample<\/a><\/p>\n

We have two tables one for header and another detail records, for example, invoice header and a item details.<\/p>\n

\r\nSELECT h.*, d.item, d.price, d.quantity, d.detail_subtotal, d.detail_vat, d.detail_total\r\nFROM t_trans AS h\r\nINNER JOIN t_trans_detail AS d \r\nON h.trans_id = d.trans_id;\r\n<\/pre>\n
So, that we get:
\n\"query\"<\/a><\/p>\n
If we design a report to show the transactions:<\/p>\n
\"\"<\/a><\/p>\n
We will get a listing of grouped transactions like this:<\/p>\n
\"\"<\/a><\/p>\n
The report footer shows the problem and solution includes some VBA code below to do the correct calculations.<\/p>\n
\r\nOption Compare Database\r\nOption Explicit\r\n\r\nDim myTrans As New Collection\r\nDim mySums(1 To 2) As Double\r\n'1 sum of total cost\r\n'2 sum of grandtotal\r\n'distinct transactions\r\n\r\nPrivate Sub myCalculation(lTransID As Long, myCost As Currency, myTotal As Currency)\r\nOn Error Resume Next\r\n    myTrans.Add lTransID, \"T\" & lTransID\r\n    'try to insert transaction ID with collection Key\r\n    If Err Then 'exists\r\n        Err.Clear\r\n    Else 'first time, so consider distinct headers\r\n        mySums(1) = mySums(1) + myCost\r\n        mySums(2) = mySums(2) + myTotal\r\n    End If\r\nEnd Sub\r\n\r\nPrivate Sub Detail_Format(Cancel As Integer, FormatCount As Integer)\r\n    Debug.Print \"Trans ID: \" & Nz(Me.trans_id, 0)\r\n    'you will see this subroutine is called more than once\r\n    Call myCalculation(Me.trans_id, Nz(Me.txt_totalcost, 0), Nz(Me.txt_grandtotal, 0))\r\nEnd Sub\r\n\r\nPrivate Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)\r\n    Me.txt_sum_totalcost = mySums(1)\r\n    Me.txt_sum_grandtotal = mySums(2)\r\nEnd Sub\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
The problem: When we use formulas on text boxes 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.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[15,33,17],"class_list":["post-59","post","type-post","status-publish","format-standard","hentry","category-technology","tag-access","tag-sql","tag-vba"],"_links":{"self":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/59","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=59"}],"version-history":[{"count":3,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/59\/revisions"}],"predecessor-version":[{"id":984,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/59\/revisions\/984"}],"wp:attachment":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=59"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=59"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=59"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}