Solved

Open Invoice Balance by Customer Query Issue

Posted on 2010-08-25
5
453 Views
Last Modified: 2012-06-27
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
Comment
Question by:avalonwgi
  • 3
  • 2
5 Comments
 
LVL 3

Expert Comment

by:Kevin Aleshire
ID: 33525794
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
 
LVL 44

Expert Comment

by:GRayL
ID: 33525962
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
 
LVL 3

Accepted Solution

by:
Kevin Aleshire earned 250 total points
ID: 33525993
Here's it is in mdb format, sorry about that.
Example2.mdb
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 250 total points
ID: 33526068
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
 
LVL 44

Expert Comment

by:GRayL
ID: 33562223
Thanks, glad to help.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now