Need Running Balance

  • Thread starter Thread starter Jan Il
  • Start date Start date
J

Jan Il

Hi all - Access 2002XP - WinME

I have a form based upon the query below. I have a control in which I wish
to display a running balance of all transactions in date order.

I have tried setting the code for the calculations for this as below,
however, what I am getting is not a running balance, but, the same current
total balance displayed for each transaction date.

SELECT tblSaveAcct.TransactionDate, tblSaveAcct.TransactionType,
tblSaveAcct.WithdrawAmt, tblSaveAcct.DepositAmt, tblSaveAcct.InterestAmt,
tblSaveAcct.Comment, tblSaveAcct.TransactionSign, tblSaveAcct.BeginBal,
Sum(Nz([DepositAmt],0)-Nz([WithdrawAmt],0)+Nz([InterestAmt])+([BeginBal],0))
AS RunningBalance
FROM tblSaveAcct
ORDER BY tblSaveAcct.TransactionDate DESC;

I would truly appreciate any suggestions, or a Help or KB article that might
explain how to achieve a running balance for each transaction date.

Jan :)
 
Your query calculates running balance for all records and displays same
amount in each record.
You'll have to break it down using a GroupBy/Sum query.
What you want to do is calculcate running balance on a 'Per Record' basis,
or
a per transactiondate basis, not?

Try to paste below statement (untested of course) in the SQL code grid
of an empty query window:

SELECT tblSaveAcct.TransactionDate,
Sum(Nz([DepositAmt],0))-Sum(Nz([WithdrawAmt],0))+
Sum(Nz([InterestAmt])+([BeginBal],0))
AS RunningBalance
FROM tblSaveAcct
GROUP BY tblSaveAcct.TransactionDate
ORDER BY tblSaveAcct.TransactionDate DESC;

Krgrds,
Perry
 
I have changed your SQL to give you a running balance by
transaction date. Have a play with it and see if it works

SELECT T1.TransactionDate,
T1.TransactionType,T1.WithdrawAmt,T1.DepositAmt,
T1.InterestAmt,T1.Comment, T1.TransactionSign,T1.BeginBal,
T3.RunningBalance
FROM tblSaveAcct T1,
(SELECT TransactionDate , Sum(DepositAmt + InterestAmt -
WithdrawAmt + BeginBal) AS RunningBalance FROM tblSaveAcct
T2 GROUP BY TransactionDate) T3
WHERE T1.transactionDate = T3.transactionDate
ORDER BY T1.TransactionDate DESC;

Hope This Helps
Gerald Stanley MCSD
 
Hi Perry,

Perry said:
Your query calculates running balance for all records and displays same
amount in each record.
You'll have to break it down using a GroupBy/Sum query.
What you want to do is calculcate running balance on a 'Per Record' basis,
or
a per transactiondate basis, not?

Try to paste below statement (untested of course) in the SQL code grid
of an empty query window:

SELECT tblSaveAcct.TransactionDate,
Sum(Nz([DepositAmt],0))-Sum(Nz([WithdrawAmt],0))+
Sum(Nz([InterestAmt])+([BeginBal],0))
AS RunningBalance
FROM tblSaveAcct
GROUP BY tblSaveAcct.TransactionDate
ORDER BY tblSaveAcct.TransactionDate DESC;
Click to expand...

I have copied and pasted the above code into an empty query, and when I
tried to open it, it crashed the db. I got the message box that said it had
caused an error and that it would close, and asked if I wanted to send the
error report to MS, which I did. I had this same problem several times when
trying to find a solution as well. I don't know why... ??

Thank you very much for your time and assistance with this issue, I truly do
appreciate it.

Jan :)
Jan Il said:
Hi all - Access 2002XP - WinME

I have a form based upon the query below. I have a control in which I wish
to display a running balance of all transactions in date order.

I have tried setting the code for the calculations for this as below,
however, what I am getting is not a running balance, but, the same current
total balance displayed for each transaction date.

SELECT tblSaveAcct.TransactionDate, tblSaveAcct.TransactionType,
tblSaveAcct.WithdrawAmt, tblSaveAcct.DepositAmt, tblSaveAcct.InterestAmt,
tblSaveAcct.Comment, tblSaveAcct.TransactionSign, tblSaveAcct.BeginBal,
Click to expand...
Sum(Nz([DepositAmt],0)-Nz([WithdrawAmt],0)+Nz([InterestAmt])+([BeginBal],0))
AS RunningBalance
FROM tblSaveAcct
ORDER BY tblSaveAcct.TransactionDate DESC;

I would truly appreciate any suggestions, or a Help or KB article that might
explain how to achieve a running balance for each transaction date.

Jan :)
Click to expand...
Click to expand...
 
Hi Gerald,

I have changed your SQL to give you a running balance by
transaction date. Have a play with it and see if it works

SELECT T1.TransactionDate,
T1.TransactionType,T1.WithdrawAmt,T1.DepositAmt,
T1.InterestAmt,T1.Comment, T1.TransactionSign,T1.BeginBal,
T3.RunningBalance
FROM tblSaveAcct T1,
(SELECT TransactionDate , Sum(DepositAmt + InterestAmt -
WithdrawAmt + BeginBal) AS RunningBalance FROM tblSaveAcct
T2 GROUP BY TransactionDate) T3
WHERE T1.transactionDate = T3.transactionDate
ORDER BY T1.TransactionDate DESC;

I have copied and pasted your code into an empty query, and tried it with
the form. It seems to work well with all, except that it does not display a
running balance. There is nothing in the RunningBalance when I open the
query or in the control on the form.

Thank you for your time and help with this issue, I really appreciate it
very much.

Jan :)
-----Original Message-----
Hi all - Access 2002XP - WinME

I have a form based upon the query below. I have a control in which I wish
to display a running balance of all transactions in date order.

I have tried setting the code for the calculations for this as below,
however, what I am getting is not a running balance, but, the same current
total balance displayed for each transaction date.

SELECT tblSaveAcct.TransactionDate, tblSaveAcct.TransactionType,
tblSaveAcct.WithdrawAmt, tblSaveAcct.DepositAmt, tblSaveAcct.InterestAmt,
tblSaveAcct.Comment, tblSaveAcct.TransactionSign, tblSaveAcct.BeginBal,
Sum(Nz([DepositAmt],0)-Nz([WithdrawAmt],0)+Nz([InterestAmt])+([BeginBal],0)
Click to expand...
)
AS RunningBalance
FROM tblSaveAcct
ORDER BY tblSaveAcct.TransactionDate DESC;

I would truly appreciate any suggestions, or a Help or KB article that might
explain how to achieve a running balance for each transaction date.

Jan :)


.
Click to expand...
 
I have copied and pasted the above code into an empty query, and when I
tried to open it, it crashed the db.
Click to expand...
HAHAHAHA
Was that a perfect example of piece of voluntary, well meant and sincere
assistence, or wot??
Keep up sending 'em reports to MS, i'd say LOL

Ok, seriously:
I don't want to mess up yr project again, but try to catch the concept of
what I forwarded.
In query design, try to play around with the aggregation/group by query.
If you don't know how to design one, look at the query design toolbar and
pick out
the Sum button (button with the sigma icon)

The main issue here, is to utilize the GroupBy functionallity to the
TransactionDate field as first field in your query and additionally add more
fields to the query grid, play around with the aggregation functions to the
fields and you will wind up getting the desired result.

And when you come to that stage, compare the SQL code with the one I
forwarded
in my previous msg.
It will be practically same ...

Repost if you get stuck doing so, but start up a new thread.

Krgrds,
Perry


Jan Il said:
Hi Perry,

Perry said:
Your query calculates running balance for all records and displays same
amount in each record.
You'll have to break it down using a GroupBy/Sum query.
What you want to do is calculcate running balance on a 'Per Record' basis,
or
a per transactiondate basis, not?

Try to paste below statement (untested of course) in the SQL code grid
of an empty query window:

SELECT tblSaveAcct.TransactionDate,
Sum(Nz([DepositAmt],0))-Sum(Nz([WithdrawAmt],0))+
Sum(Nz([InterestAmt])+([BeginBal],0))
AS RunningBalance
FROM tblSaveAcct
GROUP BY tblSaveAcct.TransactionDate
ORDER BY tblSaveAcct.TransactionDate DESC;
Click to expand...

I have copied and pasted the above code into an empty query, and when I
tried to open it, it crashed the db. I got the message box that said it had
caused an error and that it would close, and asked if I wanted to send the
error report to MS, which I did. I had this same problem several times when
trying to find a solution as well. I don't know why... ??

Thank you very much for your time and assistance with this issue, I truly do
appreciate it.

Jan :)Sum(Nz([DepositAmt],0)-Nz([WithdrawAmt],0)+Nz([InterestAmt])+([BeginBal],0))
Click to expand...
 
I am surprised that it did not show anything. I tried it
here and it showed a value, albeit I am not sure if it is
the value that you are after. Give the following a try -
on my machine it gives a running balance from the earliest
transaction date assuming that there is only one row that
has an BeginBal value.

SELECT T1.TransactionDate,
T1.TransactionType,T1.WithdrawAmt,T1.DepositAmt,
T1.InterestAmt,T1.Comment, T1.TransactionSign,T1.BeginBal,
Sum(T3.RunningBalance) As RunningBalance
FROM tblSaveAcct T1,
(SELECT TransactionDate , Sum(DepositAmt + InterestAmt -
WithdrawAmt + BeginBal) AS RunningBalance FROM tblSaveAcct
T2 GROUP BY TransactionDate) T3
WHERE T1.transactionDate >= T3.transactionDate
GROUP BY T1.TransactionDate,
T1.TransactionType,T1.WithdrawAmt,T1.DepositAmt,
T1.InterestAmt,T1.Comment, T1.TransactionSign,T1.BeginBal
ORDER BY T1.TransactionDate DESC;

If it still doesn't work, then you could e-mail me a copy
of your database(as a ZIP file).

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hi Gerald,

I have changed your SQL to give you a running balance by
transaction date. Have a play with it and see if it works

SELECT T1.TransactionDate,
T1.TransactionType,T1.WithdrawAmt,T1.DepositAmt,
T1.InterestAmt,T1.Comment, T1.TransactionSign,T1.BeginBal,
T3.RunningBalance
FROM tblSaveAcct T1,
(SELECT TransactionDate , Sum(DepositAmt + InterestAmt -
WithdrawAmt + BeginBal) AS RunningBalance FROM tblSaveAcct
T2 GROUP BY TransactionDate) T3
WHERE T1.transactionDate = T3.transactionDate
ORDER BY T1.TransactionDate DESC;

I have copied and pasted your code into an empty query, and tried it with
the form. It seems to work well with all, except that it does not display a
running balance. There is nothing in the RunningBalance when I open the
query or in the control on the form.

Thank you for your time and help with this issue, I really appreciate it
very much.

Jan :)
-----Original Message-----
Hi all - Access 2002XP - WinME

I have a form based upon the query below. I have a control in which I wish
to display a running balance of all transactions in date order.

I have tried setting the code for the calculations for this as below,
however, what I am getting is not a running balance, but, the same current
total balance displayed for each transaction date.

SELECT tblSaveAcct.TransactionDate, tblSaveAcct.TransactionType,
tblSaveAcct.WithdrawAmt, tblSaveAcct.DepositAmt, tblSaveAcct.InterestAmt,
tblSaveAcct.Comment, tblSaveAcct.TransactionSign, tblSaveAcct.BeginBal,
Sum(Nz([DepositAmt],0)-Nz([WithdrawAmt],0)+Nz([InterestAmt])+([BeginBal],0)
Click to expand...
)
AS RunningBalance
FROM tblSaveAcct
ORDER BY tblSaveAcct.TransactionDate DESC;

I would truly appreciate any suggestions, or a Help or KB article that might
explain how to achieve a running balance for each transaction date.

Jan :)


.
Click to expand...


.
Click to expand...
 
Perry said:
HAHAHAHA
Was that a perfect example of piece of voluntary, well meant and sincere
assistence, or wot??
;-)

Keep up sending 'em reports to MS, i'd say LOL
Click to expand...

I know....but...I like to give them something new and profound to ponder now
and then besides "why is there air?" ;-))
Ok, seriously:
I don't want to mess up yr project again, but try to catch the concept of
what I forwarded.
In query design, try to play around with the aggregation/group by query.
If you don't know how to design one, look at the query design toolbar and
pick out
the Sum button (button with the sigma icon)

The main issue here, is to utilize the GroupBy functionallity to the
TransactionDate field as first field in your query and additionally add more
fields to the query grid, play around with the aggregation functions to the
fields and you will wind up getting the desired result.

And when you come to that stage, compare the SQL code with the one I
forwarded
in my previous msg.
It will be practically same ...
Click to expand...
Repost if you get stuck doing so, but start up a new thread.
Click to expand...

Hmmm....well....I dunno.....this one doesn't seem quite long enough yet. ;-)

Thank you for your help, I will give your suggestion a try..... :)

Cheers!

Jan :)
Jan Il said:
Hi Perry,

Perry said:
Your query calculates running balance for all records and displays same
amount in each record.
You'll have to break it down using a GroupBy/Sum query.
What you want to do is calculcate running balance on a 'Per Record' basis,
or
a per transactiondate basis, not?

Try to paste below statement (untested of course) in the SQL code grid
of an empty query window:

SELECT tblSaveAcct.TransactionDate,
Sum(Nz([DepositAmt],0))-Sum(Nz([WithdrawAmt],0))+
Sum(Nz([InterestAmt])+([BeginBal],0))
AS RunningBalance
FROM tblSaveAcct
GROUP BY tblSaveAcct.TransactionDate
ORDER BY tblSaveAcct.TransactionDate DESC;
Click to expand...

I have copied and pasted the above code into an empty query, and when I
tried to open it, it crashed the db. I got the message box that said it had
caused an error and that it would close, and asked if I wanted to send the
error report to MS, which I did. I had this same problem several times when
trying to find a solution as well. I don't know why... ??

Thank you very much for your time and assistance with this issue, I
Click to expand...
truly
do
appreciate it.

Jan :)
Click to expand...
Sum(Nz([DepositAmt],0)-Nz([WithdrawAmt],0)+Nz([InterestAmt])+([BeginBal],0))
Click to expand...
 
Hi Gerald,

I am surprised that it did not show anything. I tried it
here and it showed a value, albeit I am not sure if it is
the value that you are after. Give the following a try -
on my machine it gives a running balance from the earliest
transaction date assuming that there is only one row that
has an BeginBal value.

SELECT T1.TransactionDate,
T1.TransactionType,T1.WithdrawAmt,T1.DepositAmt,
T1.InterestAmt,T1.Comment, T1.TransactionSign,T1.BeginBal,
Sum(T3.RunningBalance) As RunningBalance
FROM tblSaveAcct T1,
(SELECT TransactionDate , Sum(DepositAmt + InterestAmt -
WithdrawAmt + BeginBal) AS RunningBalance FROM tblSaveAcct
T2 GROUP BY TransactionDate) T3
WHERE T1.transactionDate >= T3.transactionDate
GROUP BY T1.TransactionDate,
T1.TransactionType,T1.WithdrawAmt,T1.DepositAmt,
T1.InterestAmt,T1.Comment, T1.TransactionSign,T1.BeginBal
ORDER BY T1.TransactionDate DESC;

I'm just not able to get the above code to work correctly, it still does not
want to display the running balance. Not sure why, I tried it several times,
but, it just wouldn't. :\

However, I decided to have a bit of play with it and one from another DB,
and came up with the following combination which does work for whatever
reason, and displays the running balance in proper order;

SELECT T.BeginBal, T.TransactionDate, T.Transaction, T.WithdrawAmt,
T.DepositAmt, T.InterestAmt, T.Comment, (SELECT SUM(Nz(DepositAmt, 0) -
Nz(WithdrawAmt, 0) + Nz(InterestAmt,0) + Nz(BeginBal,0)) FROM tblSaveAcct T1
WHERE T1.TransactionDate < T.TransactionDate
OR (T1.TransactionDate = T.TransactionDate)) AS RunningBalance
FROM tblSaveAcct AS T
ORDER BY T.TransactionDate;

Thank you so very much for all your time and help, I truly do appreciate it.
;-))

Cheers!

Jan :)
-----Original Message-----
Hi Gerald,

I have changed your SQL to give you a running balance by
transaction date. Have a play with it and see if it works

SELECT T1.TransactionDate,
T1.TransactionType,T1.WithdrawAmt,T1.DepositAmt,
T1.InterestAmt,T1.Comment, T1.TransactionSign,T1.BeginBal,
T3.RunningBalance
FROM tblSaveAcct T1,
(SELECT TransactionDate , Sum(DepositAmt + InterestAmt -
WithdrawAmt + BeginBal) AS RunningBalance FROM tblSaveAcct
T2 GROUP BY TransactionDate) T3
WHERE T1.transactionDate = T3.transactionDate
ORDER BY T1.TransactionDate DESC;

I have copied and pasted your code into an empty query, and tried it with
the form. It seems to work well with all, except that it does not display a
running balance. There is nothing in the RunningBalance when I open the
query or in the control on the form.

Thank you for your time and help with this issue, I really appreciate it
very much.

Jan :)
-----Original Message-----
Hi all - Access 2002XP - WinME

I have a form based upon the query below. I have a control in which I wish
to display a running balance of all transactions in date order.

I have tried setting the code for the calculations for this as below,
however, what I am getting is not a running balance, but, the same current
total balance displayed for each transaction date.

SELECT tblSaveAcct.TransactionDate, tblSaveAcct.TransactionType,
tblSaveAcct.WithdrawAmt, tblSaveAcct.DepositAmt, tblSaveAcct.InterestAmt,
tblSaveAcct.Comment, tblSaveAcct.TransactionSign, tblSaveAcct.BeginBal,
Sum(Nz([DepositAmt],0)-Nz([WithdrawAmt],0)+Nz([InterestAmt])+([BeginBal],0
Click to expand...
Click to expand...
)
)
AS RunningBalance
FROM tblSaveAcct
ORDER BY tblSaveAcct.TransactionDate DESC;

I would truly appreciate any suggestions, or a Help or KB article that might
explain how to achieve a running balance for each transaction date.

Jan :)


.
Click to expand...


.
Click to expand...
 
Back
Top