sum in access report with repeating invoice

[ConfusedMay]

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.

[lee123]

hi does this have a header and a footer because the way i do it is copy the total textbox and put it in the footer of the form and in the property box of the copied textbox find the control source and enter this

=sum(grandtotal)

lee123

[ConfusedMay]

hi does this have a header and a footer because the way i do it is copy the total textbox and put it in the footer of the form and in the property box of the copied textbox find the control source and enter this

=sum(grandtotal)

lee123

yes it's in group footer. But I if I use the sum, it still will give me the duplicate invoice total. what I need is, if invoice # is duplicate, take off the duplicate invoice amount when I want to do the sum.

[ConfusedMay]

yes it's in group footer. But I if I use the sum, it still will give me the duplicate invoice total. what I need is, if invoice # is duplicate, take off the duplicate invoice amount when I want to do the sum.

Can someone help me on this please....

[Stewart Ross]

Can someone help me on this please....

Hi. There is no easy way to do this within your report, as Access is not to know that in fact the invoice amount may be a repeated value which should not be counted more than once. It is possible to use VB within the detail section to increment or decrement unbound values, but I wouldn't recommend this approach.

There is a work-around at the query level which will resolve this for you. Create a Count query counting the number of invoice rows for each invoice number based on your report's recordsource query:

SELECT [queryname].[invoice number], Count([invoice number] as N from [queryname] GROUP BY [invoice number];

Save this under a suitable name. Create another new query in which you join the new Count query to the original report query on the invoice number. Include all the fields from your original report query along with the count field, N, from the count query. Save this under a suitable name, then change the report's recordsource from your original query to this new one.

In your report add the new count field N to the detail line somewhere, but set its visible property to false (unless you 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 change the control source to

=SUM([Invoice Amount])/N

-Stewart

[Stewart Ross]

Correction - control value should read

=SUM([Invoice Amount]/[N])

[ConfusedMay]

Correction - control value should read

=SUM([Invoice Amount]/[N])

thank you for the reply. I'll try it and will let you know....