asked on

Open Invoice Balance by Customer Query Issue

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.

Kevin Aleshire

here's an example using a subquery to create the "TotalPaidValue", which is then used in the final query to give you the "EndingBalance", then filter on the "EndingBalance" column with "Not 0"

example.accdb

GRayL

Try this - I don't have Access2007 but it should work barring any typo's:

SELECT a.SupplierID, a,SupplierName, b.InvoiceID, b.InvoiceDate, b.DueDate, b.OrigInvoiceValue, Sum(c.PaidValue) AS TotalPaidValue, b.OrigInvoiceValue - TotalPaidValue AS EndingBalance
FROM Supplier a INNER JOIN (Invoice b INNER JOIN InvoicePaid c
ON b.SupplierID = c.SupplierID AND b.InvoiceID = c.InvoiceID)
ON a.SupplierID = b.SupplierID
GROUP BY a.SupplierID, a,SupplierName, b.InvoiceID, b.InvoiceDate, b.DueDate, b.OrigInvoiceValue
HAVING Sum(c.PaidValue)<>b.OrigInvoiceValue

ASKER CERTIFIED SOLUTION

Kevin Aleshire

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRayL

Thanks, glad to help.