Current Balance Due

[thehikingfool]

I'm not sure of the best approach to this. I need a column that will show the
current balance of an invoice. Sometimes the customer will make several
payments so I need to see a current balance on each record line.
Here is what I have so far...

SELECT PAYMENT.PAYMENTID, PAYMENTLINK.INVOICEID, PAYMENT.BUYSELL,
INVOICE.INVOICENUMBER, PAYMENT.CREATEDATE, INVOICE.TOTALPRICE,
PAYMENTLINK.CASHDISCOUNT, PAYMENTLINK.MISCDISCOUNT, PAYMENTLINK.AMOUNTPAID
FROM INVOICE INNER JOIN (PAYMENTLINK INNER JOIN PAYMENT ON
PAYMENTLINK.PAYMENTID = PAYMENT.PAYMENTID) ON INVOICE.INVOICEID =
PAYMENTLINK.INVOICEID
GROUP BY PAYMENT.PAYMENTID, PAYMENTLINK.INVOICEID, PAYMENT.BUYSELL,
INVOICE.INVOICENUMBER, PAYMENT.CREATEDATE, INVOICE.TOTALPRICE,
PAYMENTLINK.CASHDISCOUNT, PAYMENTLINK.MISCDISCOUNT, PAYMENTLINK.AMOUNTPAID
HAVING (((PAYMENT.BUYSELL)="Y"))
ORDER BY PAYMENTLINK.INVOICEID, PAYMENT.CREATEDATE;

 

[vanderghast]

I only have identified payment.amountPaid as amount to take care of...


SELECT a.invoiceID, a.createDate, SUM(b.amountPaid)
FROM query AS a INNER JOIN query AS b
ON a.invoideID = b.invoiceID AND a.createDate >= b.createDate
GROUP BY a.invoiceID, a.createDate


where query would be replaced by the actual query you have, under the name
you would have saved it. Two places to replace it.



Vanderghast, Access MVP

 

[thehikingfool]

Thank you for your response.

I apoligize. I don't think I explained myself clearly. (I also apoligize for
the all caps in my post. I inherited this database.)

I have three tables; Payment, Paymentlink, and Invoice.

Payment contains top level payment info; PaymentID, CreateDate, Customer,
CheckNumber

PaymentLink contains; PaymentID, InvoiceID, CashDiscount, MiscDiscount,
AmountPaid

Invoice contains; InvoiceID, TotalPrice

(Tables all include additional fields but this is what I need now.)

I need columns in this order:
Payment.PaymentID, PaymentLink.InvoiceID, Payment.CreateDate,
Invoice.TotalPrice, PaymentLink.CashDiscount, PaymentLink.MiscDiscount,
PaymentLink.AmountPaid, Balance

I need to show seperate entries for each payment. Example; Invoice 123 for
$100 was piad on 9/15 in amount of $50 and 10/15 in amount of $50. So that
the balance will reflect $50 after 9/15 payment and zero after 10/15.

Where do I insert this code to get the results I need?

 

[vanderghast]

Assuming Invoice.TotalPrice appears correctly on each row of your query:



SELECT a.invoiceID, a.createDate, LAST(a.TotalPrice) - SUM(b.amountPaid)
FROM query AS a INNER JOIN query AS b
ON a.invoideID = b.invoiceID AND a.createDate >= b.createDate
GROUP BY a.invoiceID, a.createDate


You can add missing field that you still want by adding them in the SELECT
clause, with the LAST aggregate:





SELECT a.invoiceID, a.createDate,
LAST(a.customer),
LAST(a.amoungPaid),
LAST(a.TotalPrice) - SUM(b.amountPaid)

FROM query AS a INNER JOIN query AS b
ON a.invoideID = b.invoiceID AND a.createDate >= b.createDate

GROUP BY a.invoiceID, a.createDate


There should be no problem if there is no more than ONE record per
invoiceID, per createDate.

Vanderghast, Access MVP

 

[thehikingfool]

I am getting the message "JOIN expression not supported" and it highlights
PAYMENT.CREATEDATE >= PAYMENT.CREATEDATE

Here is what I entered:

SELECT PAYMENTLINK.INVOICEID, PAYMENT.CREATEDATE,
Last(INVOICE.TOTALPRICE)-Sum(PAYMENTLINK.AMOUNTPAID) AS BALANCE
FROM INVOICE INNER JOIN (PAYMENT INNER JOIN PAYMENTLINK ON PAYMENT.PAYMENTID
= PAYMENTLINK.PAYMENTID) ON INVOICE.INVOICEID = PAYMENTLINK.INVOICEID AND
PAYMENT.CREATEDATE >= PAYMENT.CREATEDATE
GROUP BY PAYMENTLINK.INVOICEID, PAYMENT.CREATEDATE

 

[John W. Vinson]

I am getting the message "JOIN expression not supported" and it highlights
PAYMENT.CREATEDATE >= PAYMENT.CREATEDATE

That is failing because you're trying to compare the field to itself! Surely
one of these should be PAYMENTLINK insteadl of PAYMENT. I'm not sure of the
relationships here so I can't say which.

 

[thehikingfool]

The Payment table holds information related to the check, i.e; PaymentID,
Check#, Customer, CreateDate (date of payment).

The PaymentLink table holds details of the payment, i.e; PaymentID,
InvoiceID, AmountPaid, CashDiscount.

The Invoice table holds details of the invoice, i.e; InvoiceID, Customer,
Materials, TotalPrice, CreateDate (date Invoice was made)

The Payment table is linked to the PaymentLink table by the PaymentID, the
PaymentLink table also has an InvoiceID which links it to the Invoice table.

Note: the CreateDate in the Payment table is not the same as the CreateDate
in the Invoice table. There is no CreateDate in the PaymentLink table.

I also tried using INVOICE.CREATEDATE >= PAYMENT.CREATEDATE and
PAYMENT.CREATEDATE >= INVOICE.CREATEDATE, both result in the message "You
tried to execute a query that does not include the specified expression
'INVOICEID' as part of an aggregate function.

 

[vanderghast]

Save and don't modify you existing query (the one you mentionned at the
start of this thread) :

SELECT PAYMENT.PAYMENTID, PAYMENTLINK.INVOICEID, PAYMENT.BUYSELL,
INVOICE.INVOICENUMBER, PAYMENT.CREATEDATE, INVOICE.TOTALPRICE,
PAYMENTLINK.CASHDISCOUNT, PAYMENTLINK.MISCDISCOUNT, PAYMENTLINK.AMOUNTPAID
FROM INVOICE INNER JOIN (PAYMENTLINK INNER JOIN PAYMENT ON
PAYMENTLINK.PAYMENTID = PAYMENT.PAYMENTID) ON INVOICE.INVOICEID =
PAYMENTLINK.INVOICEID
GROUP BY PAYMENT.PAYMENTID, PAYMENTLINK.INVOICEID, PAYMENT.BUYSELL,
INVOICE.INVOICENUMBER, PAYMENT.CREATEDATE, INVOICE.TOTALPRICE,
PAYMENTLINK.CASHDISCOUNT, PAYMENTLINK.MISCDISCOUNT, PAYMENTLINK.AMOUNTPAID
HAVING (((PAYMENT.BUYSELL)="Y"))
ORDER BY PAYMENTLINK.INVOICEID, PAYMENT.CREATEDATE;


under the name of... say, Q1, then, in ANOTHER query:


SELECT a.invoiceID, a.createDate, LAST(a.TotalPrice) - SUM(b.amountPaid)
FROM q1 AS a INNER JOIN q1 AS b
ON a.invoideID = b.invoiceID AND a.createDate >= b.createDate
GROUP BY a.invoiceID, a.createDate



Vanderghast, Access MVP

 

[thehikingfool]

That did it!
Thank you very much!

Save and don't modify you existing query (the one you mentionned at the
start of this thread) :

SELECT PAYMENT.PAYMENTID, PAYMENTLINK.INVOICEID, PAYMENT.BUYSELL,
INVOICE.INVOICENUMBER, PAYMENT.CREATEDATE, INVOICE.TOTALPRICE,
PAYMENTLINK.CASHDISCOUNT, PAYMENTLINK.MISCDISCOUNT, PAYMENTLINK.AMOUNTPAID
FROM INVOICE INNER JOIN (PAYMENTLINK INNER JOIN PAYMENT ON
PAYMENTLINK.PAYMENTID = PAYMENT.PAYMENTID) ON INVOICE.INVOICEID =
PAYMENTLINK.INVOICEID
GROUP BY PAYMENT.PAYMENTID, PAYMENTLINK.INVOICEID, PAYMENT.BUYSELL,
INVOICE.INVOICENUMBER, PAYMENT.CREATEDATE, INVOICE.TOTALPRICE,
PAYMENTLINK.CASHDISCOUNT, PAYMENTLINK.MISCDISCOUNT, PAYMENTLINK.AMOUNTPAID
HAVING (((PAYMENT.BUYSELL)="Y"))
ORDER BY PAYMENTLINK.INVOICEID, PAYMENT.CREATEDATE;


under the name of... say, Q1, then, in ANOTHER query:


SELECT a.invoiceID, a.createDate, LAST(a.TotalPrice) - SUM(b.amountPaid)
FROM q1 AS a INNER JOIN q1 AS b
ON a.invoideID = b.invoiceID AND a.createDate >= b.createDate
GROUP BY a.invoiceID, a.createDate



Vanderghast, Access MVP