Hi I need some helps in calculating the invoice amount in access 2003 report. Basically I have an access report that shows invoice#, invoice amount, payment amount, and payment date. The problem is some customers paid 2 - 3 times for 1 invoices (different amount and date) so when I total the invoice amount it duplicated since the report will show the same invoice amount for 2 payments
for example:
invoice number invoice amount payment amount payment date
100 3500 500 1/1/08
100 3500 3000 1/5/08
200 1000 1000 1/5/08
what I need is when I added the total on the report, it will show total invoice amount is 4500 (since it's only 2 invoices) instead of 8000. How do I do it on the access report? Please help.
Thank you.