Access 2007 AP Report

[emberendt]

Good morning. I found this forum this morning and I hope someone can help me out. My company is looking for a report that will list purchase orders and invoices paid against them. The PO amounts and invoice amounts are housed in the same table but in separate columns. There are multiple invoices paid against every PO. In order see which invoices were paid on each PO, there is a column for PO number. The PO number gets entered in on every line. I have tried to set up a query that will deduct the invoice amount from the PO. The problem I am running into is when the query runs the PO amount pulls in for every time an invoice is paid against it. For example PO123 is $30,000 and the following invoices are paid against it: INV 3 - $5,000, INV4 - $5,000 and INV5 - $5,000. The query will return $90,000 for the PO amount and a cumulative total for the invoices of $15,000. What do I need to do? Any help would be greatly appreciated. Thank you.

[June7]

Try:

First do an aggregate query to sum the invoices by PO Number. Then join that query to the PO table.

If you need to see the invoice details then do report based on a normal query that simply joins the two tables. Use report Grouping and Sorting to arrange the records. Have a group set for the PO number and put PO data in group header. List the invoice data in the Detail section. Textbox in group footer to calculate difference. [POAmount]-Sum(InvoiceAmount)