Debtors Reports

[Kodama76]

I have a database that keeps track of orders from the company store.

There is an Invoice table that contains an invoice ID and Total Paid field among others

There is an Order table that contains the information about the items ordered in the invoice (ie. Invoice ID, Item Name, Item Cost)

I created a report sorted by Invoice ID that gives the Items ordered, totals the Item Cost field to produce a total cost amount and then subtracts the Total Paid field from that calculated field to give a total owed.

The resulting report looks great but I want to weed out those Invoices where the calculated total owed is a positive number but.....the Invoice total and the total owed are calculated within the report not in a table.

Does that make sense? I've included a copy of the database with some example invoices. Any ideas??

 

[jeremie_ingram]

I have looked at the sample and have been able to modify it based on the calculated field within your report, as well as adding a field to the query itself. What exactly are you trying to achieve here?
Do you want only those who owe to apear in the report? Do you want everyone to appear?
I assume "weed out those Invoices where the calculated total owed is a positive number" means that you ONLY WANT those to show that actually owe money or are at $0 owed.
Take a look at query 2 and 3 as well as thier reports. Let me know if im getting close.

 

[jeremie_ingram]

Heres the attachment

 

[Kodama76]

Jeremie,

Thanks very much for looking at that for me but it looks like you ran into the same issue I had. Luckily I figured this out using a second query (cross tab of orders and invoice) that provides me with a total due for the entire invoice.

Thanks again