Trouble with totals (SUM)

[ajarrell]

I have an invoice report this is grouped by Contractor. Within that, I group by Project, and the project header has an invoice amount (ASSESS). The detail section prints payments made on the project. I am able to create a total by project (in the project footer) which is the project ASSESS less all the payments. However, when I SUM(ASSESS) in the Contractor footer to get a balance due, the ASSESS amount is added for each detail. I know there must be a way to do what I'm trying to do.

Thank you in advance!

 

[Big Pat]

Hi,

So ASSESS is the invoice amount, is that right? And you have a separate field called PAYMENT?

The layout below is how I would do it. You'll need to substitute your own field names, but the important thing is to sum the payments in the right footer and then subtract that from the sum of the invoices. In plain English: 1. add up the invoices for this contractor 2. add up the payments made so far across all projects for this contractor 3. subtract one from the other and that's your balance.

I think it is clearer to show "sub-balances" in the project footer too as shown here, but that's up to you. If you want a balance ONLY at the Contractor level, then you don't need the one in the project footer.

============================================================

Contractor header
Name of Contractor

Project header
[InvoiceAmt]

Detail section
Payment
Payment
Payment

Project footer
TotalPaid: =Sum([Payment])...............ProjectBalance: = [InvoiceAmt]-Sum([Payment])

Contractor footer
ContractorBalance: = Sum([InvoiceAmt] - Sum([Payment])

=============================================================

See if that works and if I'm way off can you post a PDF of your report to help me visualise your layout?

Good luck!

 

[ajarrell]

That is pretty much what I am doing. I guess the root of the problem is in the query the report is based on. It was returning 1 record per project until I added fields from the payment table. Now, it returns 1 record per payment, and the project information is duplicated for each payment. Consequently, the sum is adding the invoice amount multiple times (if there are multiple payments). I was hoping to get around this in the report without having to modify the query.

 

[Big Pat]

OK, I see what you mean now. I had to mock this up in the attached database and - initially - I got exactly the same as you. And you're right about the reason.

I wasn't able to do anything in the report, but I added a separate query (see query 3) which summed the outstanding amounts for each contractor. Then, by joining that query to the one for the report, I was able to ignore the duplicated sum. It remains in the underlying query, but because the "answer" calculated in the new query is now available, you can use that instead.

I hope the attached example helps.

Pat.

 

[ajarrell]

Thank you! That helps a lot. I appreciate the time you took to get an answer for me.