• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 470
  • Last Modified:

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.
0
avalonwgi
Asked:
avalonwgi
  • 3
  • 2
2 Solutions
 
Kevin AleshireCommented:
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
0
 
GRayLCommented:
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
0
 
Kevin AleshireCommented:
Here's it is in mdb format, sorry about that.
Example2.mdb
0
 
GRayLCommented:
I had two typo's this works:

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;
0
 
GRayLCommented:
Thanks, glad to help.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now