Solved

Open Invoice Balance by Customer Query Issue

Posted on 2010-08-25
5
454 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

911 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

22 Experts available now in Live!

Get 1:1 Help Now