Invoice/AR Report Question

[BBDC]

Hi All-
i appologize if my question is rudimentary...i have been tasked to create an access db without a trem. amount of experience.

I have created a DB to track invoices and payments applied to said invoices.

I have one form for invoices being sent to an invoice table and one sub form for payments being sent to a payment table...joining the 2 on record id.

I am having issues with the report. As an invoice can have many payments associated with it, when i create the report using the wizard i am getting the following:

Invoice Invoice Date Invoice Amount Payment Amount
12345 03/15/07 $500.00 200.00
12345 03/15/07 $500.00 300.00

My problem...duplicate entries for invoice, invoice
date and invoice amount. I would like the report to display as such:

Invoice Invoice Date Invoice Amount Payment Amount
12345 03/15/07 $500.00 200.00
300.00

Any Suggestions would be greatly appreciated.

 

[dhookom]

Maybe place your invoice information in your main report and payment information in a subreport.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BBDC]

Thanks...worked like a charm.

 

[BBDC]

one question:

I got the subreport working great as indicated by my last post, however now i am struggeling with the issue of summing my data elements.

Say for instance i want to calculate the total payments made on an invoice and then display the variance (+/-) if any as a last line item total of each invoice listed in the report.

Invoice Invoice Date Invoice Amount Payments Variance
12345 03/15/07 $500.00 200.00
300.00

Total $500.00 $500.00 0.00

As the payment info is contained in the subreport, am i limited to summing up the [payment amont] field in the footer of the sub report or can i have it display in the footer of the report so that at the end of each invoice group it will repeat the same "total" section".

Further how can i perform a calculation on two calculated fields (one in the report detail section and one in a subreport)

Thanks....As Always!!!

 

[dhookom]

The easiest method is to create a totals query of your payments table that groups by Invoice and sums the Payments. Add this totals query to your report's record source query and join the Invoice fields. You may need to use a LEFT JOIN so all the invoices are displayed even if there are no payments.

This should make your SumOfPayments available in your main report.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]