I need some help with the calculation of invoice amounts on a Microsoft Access 2010 report. My report shows the following fields; -invoice#, invoice amount, payment amount, and payment due (i.e. payment due = payment amount - invoice amount). There are some invoice numbers have balances for their different regional offices:
for example:
Expand|Select|Wrap|Line Numbers
- Invoice# Company Site InvoiceAmt PmtAmt PmtDue
- Invoice1 A A1 2000 500 1500
- Invoice1 A A2 4000 1000 3000
- Invoice 1 A A3 1000 1000 0
- Invoice 2 B B 3000 3000 0
I created a count query called qryPmtCount to count the number of invoice rows of each invoice based on my report's recordsource query: -
Expand|Select|Wrap|Line Numbers
- SELECT qryPayments.InvoiceNumber, Count(qryPayments.[InvoiceNumber]) AS N
- FROM qryPayments
- GROUP BY qryPayments.[InvoiceNumber];
In my report, I added the new count field N to the detail line, but setted its visible property to false (because I do not want to show how many invoice payments there are for that invoice). Finally, to get the true invoice value shown on each unbound invoice total, I changed the control source to: -
Expand|Select|Wrap|Line Numbers
- =SUM([PmtDue]/[N])
Thank you in advance for your help