Report Total

[Guest]

Hi, there,

I need to report on the Amount due and Payment details by Invoice number.
The Invoice number and amount due is maintained in one table while the
Payment details are stored in another table. The common link is the invoice
number.

When I link up the two tables in a query. I know that the invoice number
and amount will be repeated for the number of payment received. However, to
repeat the invoice number is not a good way for presentation. How can I just
show one line of invoice number and invoice amount on the report while the
payment was listed beside it. Please refer to the example below:

e.g.
Invoice Invoice Amount Payment Date Payment Amount
6895 $80,000 Jan 01, 05 75,000
Jan 15, 05 5,000

6896 $15,000 Jan 01, 05 10,000
Jan 12, 05 3,000
Feb 01, 05 2,000

6897 $18,000 Jan 31, 05 18,000


There must be a easy method but I cannot figure out. Thank you.

BL

 

[AlCamp]

BL,
One your report, set the Hide Duplicates for InvoiceNo and InvoiceAmt to
YES. (I assume your grouping on InvoiceNo)
hth
Al Camp

 

[Guest]

Thank you for your advice.

If I want to have a subtotal for the Invoice level;vendor level and report
level. It is easy for the payment field. How can I subtotal the Invoice
Amount?

Once again thank you vert much for your help.

BL

 

[AlCamp]

I think you're saying that if there is 10 payments on an InvoiceNo an
InvoiceAmt of 10,00 would yield 100,000.

In your InvoiceNo footer try...
= Sum(InvoiceAmt)/Count(InvoiceNo)
I think that should work... but I'd have to test it to be sure. Give it a
go, it only takes a moment and can't hurt.
Al Camp

 

[Guest]

Thank, AlCamp,

Your answer gives me a lot of insight. Excellent.

BL