Help with running balance

[John Spencer]

Well, if you are trying to use that subquery to return a value for a field you
must A) return only one field, B) return either no record or one record, C)
surround the entire subquery with parentheses, and D) use criteria to limit
what you are returning.

OR you can use that query as the entire query. I would try that first.

SELECT a.TransDate, a.CustID, LAST(a.ChargeAmount)
, SUM(b.ChargeAmount) as RunningSum
FROM Accounts As a INNER JOIN Accounts AS b
ON a.CustID=b.CustID AND a.TransDate>= b.TransDate
GROUP BY a.TransDate, a.CustID
ORDER BY a.CustID, a.TransDate


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[inept_webmaster]

John, Thank you! Thank you!
Your suggestion to replace the entire query was just what I needed.
It works great. Only one little glitch though. I tried to make my
query as simple as possible to explain what I was needing. I was
planning to have several other fields from the accounts table included
on the query.

I actually have three more fields in the table. Instead of just the
amount field I have one field <Payments> for payments and another
<Charges> for charges. For figuring the running total I need to add
the charges and subtract the payments. Each record would contain
either a payment entry or a charge entry but not both (so some of the
fields will be NULL).
I also have a <Description> field that I need to have included on the
query.

I realize that I shouldn't be depending on you all to do my work for
me. I am working late nights cramming for this project but I'm
finding a pretty steep learning curve.

Bless you for any help you can give.
Michael

 

[inept_webmaster]

OK, I have figured out how to add the additional Description field
onto the query as follows,

SELECT a.TransDate, a.CustID, LAST(a.Description) AS Description, LAST
(a.ChargeAmount) AS Charge, SUM(b.ChargeAmount) AS ChargeBalance
FROM Accounts AS a INNER JOIN Accounts AS b ON (a.CustID=b.CustID) AND
(a.TransDate>=b.TransDate)
GROUP BY a.TransDate, a.CustID
ORDER BY a.TransDate, a.CustID;

I have noticed another problem though, Anytime there are two
transaction on the same day for the same customer the calculation gets
messed up. There is an <ID> field also in the Accounts table. Can it
be integrated somehow to alleviate this problem?

Thanks again,
Michael

 

[inept_webmaster]

One last update and I'm going to bed...

SELECT LAST(a.TransDate), a.CustID, LAST(a.Description) AS
Description, LAST(a.ChargeAmount) AS Charge, LAST(a.PaymentAmount) AS
Payment, SUM(b.ChargeAmount) AS Balance
FROM Accounts AS a INNER JOIN Accounts AS b ON (a.CustID=b.CustID) AND
(a.Transdate >=b.TransDate)
GROUP BY a.CustID, a.TransDate, a.ID
ORDER BY a.CustID, a.TransDate, a.ID;

By adding in the a.ID under the GROUP & ORDER lines it seems to have
resolved my issue with multiple transactions on the same day.
Everything is now working except for the fact that my Balance field on
the query is only summing up the Charges and not the Payments. I
naively thought I had it figured out. I replaced SUM(b.ChargeAmount)
on the first line with SUM(b.ChargeAmount - b.PaymentAmount) but with
no joy. It just shows blanks. Can you tell me why that does not
work? When I tried it as SUM(b.ChargeAmount + b.ChargeAmount) it
worked as expected....

Thanks,
Michael

 

[John W. Vinson]

By adding in the a.ID under the GROUP & ORDER lines it seems to have
resolved my issue with multiple transactions on the same day.
Everything is now working except for the fact that my Balance field on
the query is only summing up the Charges and not the Payments. I
naively thought I had it figured out. I replaced SUM(b.ChargeAmount)
on the first line with SUM(b.ChargeAmount - b.PaymentAmount) but with
no joy. It just shows blanks. Can you tell me why that does not
work? When I tried it as SUM(b.ChargeAmount + b.ChargeAmount) it
worked as expected....

If either ChargeAmount or PaymentAmount is NULL then any calculation involving
them will return NULL. Use the NZ() function to get around this...

SUM(NZ(b.ChargeAmount) - NZ(b.PaymentAmount))

to convert the NULL to ZERO.

 

[Marshall Barton]

One last update and I'm going to bed...

SELECT LAST(a.TransDate), a.CustID, LAST(a.Description) AS
Description, LAST(a.ChargeAmount) AS Charge, LAST(a.PaymentAmount) AS
Payment, SUM(b.ChargeAmount) AS Balance
FROM Accounts AS a INNER JOIN Accounts AS b ON (a.CustID=b.CustID) AND
(a.Transdate >=b.TransDate)
GROUP BY a.CustID, a.TransDate, a.ID
ORDER BY a.CustID, a.TransDate, a.ID;

By adding in the a.ID under the GROUP & ORDER lines it seems to have
resolved my issue with multiple transactions on the same day.
Everything is now working except for the fact that my Balance field on
the query is only summing up the Charges and not the Payments. I
naively thought I had it figured out. I replaced SUM(b.ChargeAmount)
on the first line with SUM(b.ChargeAmount - b.PaymentAmount) but with
no joy. It just shows blanks. Can you tell me why that does not
work? When I tried it as SUM(b.ChargeAmount + b.ChargeAmount) it
worked as expected....

That calculation can also be done using the expression:
Sum(b.ChargeAmount) - Sum(b.ChargeAmount)
without worrying about the Null issue.

Note that using Last (or First) rarely does anything more
than pick one of the values at random. I think your
LAST(a.ChargeAmount) and LAST(a.PaymentAmount) may not be
doing what you expect.