?
Solved

Open Invoice Balance by Customer Query Issue

Posted on 2010-08-25
5
Medium Priority
?
460 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

770 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