Invoice Payments Query

[Podder]

Hi,
I am trying to create a query to check what invoices have not been paid and how much is owing.
My setup is as follows:
WeeklyInvoiceTBL


ID
InvNo
Area
Customer
InvDate
Item
Quantity

PaymentsTBL
InvNo
Area
InvDate
TotalAmount
PayDate

ItemsTBL
ID
Item
Cost

I have then made a query which as the following
InvoiceTotalsQry
InvNo
InvDate
Area
SumOfQuantity
Item
Cost
GrossAmount

What Im trying to ahcieve is a Query that will show all invoices sent and also show invoices that have been paid.
When an Invoice has been paid, show 0, when an invoice has not been paid, show GrossAmount.
Currently, I have a query which shows all invoices paid but not the unpaid ones.

Is this possible.

Thanks

[CJ_London]

I have a query which shows all invoices paid but not the unpaid ones.

would be helpful to see your query sql but suspect you need a left join on InvNo between WeeklyInvoiceTBL and PaymentsTBL

When an Invoice has been paid, show 0, when an invoice has not been paid, show GrossAmount.

I don't see an invoice total amount, but assuming there is one deduct paid amount from this value

[Podder]

Ajax,
Invoice total = GrossAmount (sorry for the lack of clarification)
The problem I am having is the InvoiceNo in the InvoiceTotalsQry is not present in the PaymentsTBL, Im trying to determine if theres a way of putting all invoiceNo's in the query, then doing as you suggested.

[Minty]

Create a query that joins the items to the invoices, use this to generate the total cost.
It's very unusual not to store invoice lines with prices and a line total, as prices can change over time, and you may offer a line discount?

Use that query with a left join to your payments.
Create a calculated field taking the payment from your invoice total.

You should now have all invoices and the total outstanding.
Either filter by = 0 for paid one or > 0 for unpaid ones

[CJ_London]

The problem I am having is the InvoiceNo in the InvoiceTotalsQry is not present in the PaymentsTBL

so why are you showing it?

PaymentsTBL
InvNo
Area
InvDate
TotalAmount
PayDate

[mike60smart]

HiCan you upload a copy of the database?

[Podder]

Create a query that joins the items to the invoices, use this to generate the total cost.
It's very unusual not to store invoice lines with prices and a line total, as prices can change over time, and you may offer a line discount?

Use that query with a left join to your payments.
Create a calculated field taking the payment from your invoice total.

You should now have all invoices and the total outstanding.
Either filter by = 0 for paid one or > 0 for unpaid ones

Minty,
Thanks, your solution worked.