Open Invoice Balance by Customer Query Issue
Posted on 2010-08-25
Tables and Fields are:
Supplier: SupplierID, SupplierName
Invoice: SupplierID, InvoiceID, InvoiceDate, DueDate, OrigInvoiceValue
InvoicePaid: SupplierID, InvoiceID, PaidValue
The end query should be this
SupplierID; SupplierName, InvoiceID, InvoiceDate, DueDate, OrigInvoiceValue, "TotalPaidValue", EndingBalance
THe query should filter out Ending Balance that = 0, Show only Invoices with remaining balances on them.
As you can see I need to sum the payments made for a Supplier and Invoice, Then calculate ending Balance with OrigInvoiceValue-SumofPayments for that Invoice by Supplier.