Running Balance in Access 2007

[Silvio]

Hello I have the following query with 3 columns:

SELECT tblRegister.TransID, tblRegister.Payment, tblRegister.Deposit
FROM tblRegister;

How can I have a running balance? Both columns, Payment and Deposit are
positives numbers and each raw will always have either or (never both),
Payments will be subtracted from the running balance.
This is what I would like to have:

TransID Deposit Payment Balance
1 1,000 1,000
2 600 400
3 10 410
4 30 380

And so forth... Also, I need the query to be updatable, I need to be able
to place a checkmark in the Reconcile Check Box. Can it be done?

[Gina Whipp]

Silvo,

Add this to your query (on the *Field* line of your last column)...

RunSum: DSum("Deposit","tblRegister","TransID <= " &
[TransID])-DSum("Payment","tblRegister","TransID <= " & [TransID])


However, I do not see where Reconciled is part of your query. Is it in the
same table as tblRegister?
--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Silvio" <***@discussions.microsoft.com> wrote in message news:D62DF603-059E-4836-8F54-***@microsoft.com...
Hello I have the following query with 3 columns:

SELECT tblRegister.TransID, tblRegister.Payment, tblRegister.Deposit
FROM tblRegister;

How can I have a running balance? Both columns, Payment and Deposit are
positives numbers and each raw will always have either or (never both),
Payments will be subtracted from the running balance.
This is what I would like to have:

TransID Deposit Payment Balance
1 1,000 1,000
2 600 400
3 10 410
4 30 380

And so forth... Also, I need the query to be updatable, I need to be able
to place a checkmark in the Reconcile Check Box. Can it be done?

[jk-can]

Gina Whipp wrote on 03/04/2010 16:32 ET

Post by Gina Whipp
Silvo
Add this to your query (on the *Field* line of your last column)..
RunSum: DSum("Deposit","tblRegister","TransID <
"
[TransID])-DSum("Payment","tblRegister","TransID <
" & [TransID]
However, I do not see where Reconciled is part of your query. Is it in th
same table as tblRegister
Gina Whip
2010 Microsoft MVP (Access
"I feel I have been denied critical, need to know, information!"
Tremor
I
http://www.regina-whipp.com/index_files/TipList.ht
"Silvio" wrote in messag
news
Hello I have the following query with 3 columns
SELECT tblRegister.TransID, tblRegister.Payment, tblRegister.Deposi
FROM tblRegister
How can I have a running balance? Both columns, Payment and Deposit ar
positives numbers and each raw will always have either or (never both)
Payments will be subtracted from the running balance
This is what I would like to have
TransID Deposit Payment Balanc
1 1,000 1,00
2 600 40
3 10 41
4 30 38
And so forth... Also, I need the query to be updatable, I need to be abl
to place a checkmark in the Reconcile Check Box. Can it be done

Hi, Ms.Gina

I tried your formula "RunSum
DSum(&quot;Deposit&quot;,&quot;tblRegister&quot;,&quot;TransI
&lt;=&quot
&amp;[TransID])-DSum(&quot;Payment&quot;,&quot;tblRegister&quot;,&quot;TransI
&lt;=&quot; &amp; [TransID])" and it works

But, what if I use the "Transaction Date" or Date instead of a
"ID". Because when I arranged the data base from the the date th
running balance gave me the wrong answer because it added or subtracted amoun
from previous data based from the ID not the date (In my case, there ar
instances that the latest date may have older/lower ID)

Please help me fix this

Thank you very much
JK Can

[KenSheridan via AccessMonster.com]

Usually with this sort of 'statement' query a transaction date would be used
to order the rows and compute the balances rather than the TransactionID
number. This caters for transactions not necessarily being entered in order
of occurrence, and allows for transactions on the same day to be ordered by
credits first, though this traditional practice, which was originally
intended to prevent an account going into debit where the daily closing
balance was in credit, tends not to be followed these days, which of course
works to the bank's advantage, not the customer's!

Here's an example of an updatable query which does this, returning the rows
in date order, with credits on the same date shown before debits. Within a
set of credits or debits on the same day rows are sorted arbitrarily by
TransactionID:

SELECT TransactionDate, Credit, Debit,
DSUM("Nz(Credit,0)-Nz(Debit,0)","Transactions","TransactionDate <=

" & FORMAT(TransactionDate,"\#yyyy-mm-dd\#") & "
AND (TransactionID <= " & TransactionID & " OR TransactionDate <>
" & FORMAT(TransactionDate,"\#yyyy-mm-dd\#") & ")") AS Balance
FROM Transactions
ORDER BY TransactionDate, IIf(Credit>Debit,0,1), TransactionID;

Note that the DCount function is called only once per row by summing the
credit minus debit per transaction, using the Nz function to return a zero
for Null debits or credits..

Ken Sheridan
Stafford, England

Post by Silvio
SELECT tblRegister.TransID, tblRegister.Payment, tblRegister.Deposit
FROM tblRegister;
How can I have a running balance? Both columns, Payment and Deposit are
positives numbers and each raw will always have either or (never both),
Payments will be subtracted from the running balance.
TransID Deposit Payment Balance
1 1,000 1,000
2 600 400
3 10 410
4 30 380
And so forth... Also, I need the query to be updatable, I need to be able
to place a checkmark in the Reconcile Check Box. Can it be done?

[Silvio]

Thank you Ken this is exactly what I needed. However, I have hard time to
format the balance as Currency. Can you help?

Post by KenSheridan via AccessMonster.com
Usually with this sort of 'statement' query a transaction date would be used
to order the rows and compute the balances rather than the TransactionID
number. This caters for transactions not necessarily being entered in order
of occurrence, and allows for transactions on the same day to be ordered by
credits first, though this traditional practice, which was originally
intended to prevent an account going into debit where the daily closing
balance was in credit, tends not to be followed these days, which of course
works to the bank's advantage, not the customer's!
Here's an example of an updatable query which does this, returning the rows
in date order, with credits on the same date shown before debits. Within a
set of credits or debits on the same day rows are sorted arbitrarily by
SELECT TransactionDate, Credit, Debit,
DSUM("Nz(Credit,0)-Nz(Debit,0)","Transactions","TransactionDate <=
" & FORMAT(TransactionDate,"\#yyyy-mm-dd\#") & "
AND (TransactionID <= " & TransactionID & " OR TransactionDate <>
" & FORMAT(TransactionDate,"\#yyyy-mm-dd\#") & ")") AS Balance
FROM Transactions
ORDER BY TransactionDate, IIf(Credit>Debit,0,1), TransactionID;
Note that the DCount function is called only once per row by summing the
credit minus debit per transaction, using the Nz function to return a zero
for Null debits or credits..
Ken Sheridan
Stafford, England

Post by Silvio
SELECT tblRegister.TransID, tblRegister.Payment, tblRegister.Deposit
FROM tblRegister;
How can I have a running balance? Both columns, Payment and Deposit are
positives numbers and each raw will always have either or (never both),
Payments will be subtracted from the running balance.
TransID Deposit Payment Balance
1 1,000 1,000
2 600 400
3 10 410
4 30 380
And so forth... Also, I need the query to be updatable, I need to be able
to place a checkmark in the Reconcile Check Box. Can it be done?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1
.

[Silvio]

Ken, I also noticed that if I filter data (e.g. Saving or Checking) the
running balance is wrong any idea why is that? Below is my query with your
code bulti in:

SELECT tblRegister.TransID, tblRegister.TransDate, tblRegister.TransNum,
tblRegister.TransMemo, tblRegister.Payment, tblRegister.Clr,
tblRegister.Deposit,
DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","TransDate <=

" & Format(TransDate,"\#yyyy-mm-dd\#") & "
AND (TransID <= " & TransID & " OR TransDate <>
" & Format(TransDate,"\#yyyy-mm-dd\#") & ")") AS Balance
FROM tblRegister
WHERE (((tblRegister.Account_ID)=[Forms]![Register]![cmbAccount]))
ORDER BY tblRegister.TransDate, IIf(Deposit>Payment,0,1), tblRegister.TransID;

Post by KenSheridan via AccessMonster.com
Usually with this sort of 'statement' query a transaction date would be used
to order the rows and compute the balances rather than the TransactionID
number. This caters for transactions not necessarily being entered in order
of occurrence, and allows for transactions on the same day to be ordered by
credits first, though this traditional practice, which was originally
intended to prevent an account going into debit where the daily closing
balance was in credit, tends not to be followed these days, which of course
works to the bank's advantage, not the customer's!
Here's an example of an updatable query which does this, returning the rows
in date order, with credits on the same date shown before debits. Within a
set of credits or debits on the same day rows are sorted arbitrarily by
SELECT TransactionDate, Credit, Debit,
DSUM("Nz(Credit,0)-Nz(Debit,0)","Transactions","TransactionDate <=
" & FORMAT(TransactionDate,"\#yyyy-mm-dd\#") & "
AND (TransactionID <= " & TransactionID & " OR TransactionDate <>
" & FORMAT(TransactionDate,"\#yyyy-mm-dd\#") & ")") AS Balance
FROM Transactions
ORDER BY TransactionDate, IIf(Credit>Debit,0,1), TransactionID;
Note that the DCount function is called only once per row by summing the
credit minus debit per transaction, using the Nz function to return a zero
for Null debits or credits..
Ken Sheridan
Stafford, England

Post by Silvio
SELECT tblRegister.TransID, tblRegister.Payment, tblRegister.Deposit
FROM tblRegister;
How can I have a running balance? Both columns, Payment and Deposit are
positives numbers and each raw will always have either or (never both),
Payments will be subtracted from the running balance.
TransID Deposit Payment Balance
1 1,000 1,000
2 600 400
3 10 410
4 30 380
And so forth... Also, I need the query to be updatable, I need to be able
to place a checkmark in the Reconcile Check Box. Can it be done?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1
.

[KenSheridan via AccessMonster.com]

That sounds as if you are using the same table for more than one account,
which is unusual. I would normally have separate tables for each of my
current (checking) and saving accounts. But using one table for all you'd
need a column in the in the table to identify the account. I assume that the
AccountID performs this task, so you'd need to build this into the DSum
function's criteria so that it references the cmbAccount control also:

SELECT tblRegister.TransID, tblRegister.TransDate, tblRegister.TransNum,
tblRegister.TransMemo, tblRegister.Payment, tblRegister.Clr,
tblRegister.Deposit,
DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","Account_ID) = " &
[Forms]![Register]![cmbAccount] & " AND TransDate <=
" & Format(TransDate,"\#yyyy-mm-dd\#") & "
AND (TransID <= " & TransID & " OR TransDate <>
" & Format(TransDate,"\#yyyy-mm-dd\#") & ")") AS Balance
FROM tblRegister
WHERE Account_ID = [Forms]![Register]![cmbAccount]
ORDER BY tblRegister.TransDate, IIf(Deposit>Payment,0,1), tblRegister.
TransID;

BTW if you see any blank lines in the above that's purely the result of the
newsreader breaking a line. You can remove the blank line from the query.

Ken Sheridan
Stafford, England

Post by Silvio
Ken, I also noticed that if I filter data (e.g. Saving or Checking) the
running balance is wrong any idea why is that? Below is my query with your
SELECT tblRegister.TransID, tblRegister.TransDate, tblRegister.TransNum,
tblRegister.TransMemo, tblRegister.Payment, tblRegister.Clr,
tblRegister.Deposit,
DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","TransDate <=
" & Format(TransDate,"\#yyyy-mm-dd\#") & "
AND (TransID <= " & TransID & " OR TransDate <>
" & Format(TransDate,"\#yyyy-mm-dd\#") & ")") AS Balance
FROM tblRegister
WHERE (((tblRegister.Account_ID)=[Forms]![Register]![cmbAccount]))
ORDER BY tblRegister.TransDate, IIf(Deposit>Payment,0,1), tblRegister.TransID;

Post by KenSheridan via AccessMonster.com
Usually with this sort of 'statement' query a transaction date would be used
to order the rows and compute the balances rather than the TransactionID

[quoted text clipped - 44 lines]

Post by KenSheridan via AccessMonster.com

Post by Silvio
And so forth... Also, I need the query to be updatable, I need to be able
to place a checkmark in the Reconcile Check Box. Can it be done?

[KenSheridan via AccessMonster.com]

Oops, an extra parenthesis crept in. It should have been:

SELECT tblRegister.TransID, tblRegister.TransDate, tblRegister.TransNum,
tblRegister.TransMemo, tblRegister.Payment, tblRegister.Clr,
tblRegister.Deposit,
DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","Account_ID = " &
[Forms]![Register]![cmbAccount] & " AND TransDate <=
" & Format(TransDate,"\#yyyy-mm-dd\#") & "
AND (TransID <= " & TransID & " OR TransDate <>
" & Format(TransDate,"\#yyyy-mm-dd\#") & ")") AS Balance
FROM tblRegister
WHERE Account_ID = [Forms]![Register]![cmbAccount]
ORDER BY tblRegister.TransDate, IIf(Deposit>Payment,0,1), tblRegister.
TransID;

I've assumed AccountID is a number data type. If its text amend the above:

DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","Account_ID = """ &
[Forms]![Register]![cmbAccount] & """ AND TransDate <=

Ken Sheridan
Stafford, England

[Silvio]

Thanks Ken, that works. Is there a way to format the Balance as Currency
(e.g. $ 15,512.15)?

Post by Silvio
SELECT tblRegister.TransID, tblRegister.TransDate, tblRegister.TransNum,
tblRegister.TransMemo, tblRegister.Payment, tblRegister.Clr,
tblRegister.Deposit,
DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","Account_ID = " &
[Forms]![Register]![cmbAccount] & " AND TransDate <=
" & Format(TransDate,"\#yyyy-mm-dd\#") & "
AND (TransID <= " & TransID & " OR TransDate <>
" & Format(TransDate,"\#yyyy-mm-dd\#") & ")") AS Balance
FROM tblRegister
WHERE Account_ID = [Forms]![Register]![cmbAccount]
ORDER BY tblRegister.TransDate, IIf(Deposit>Payment,0,1), tblRegister.
TransID;
DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","Account_ID = """ &
[Forms]![Register]![cmbAccount] & """ AND TransDate <=
Ken Sheridan
Stafford, England
--
Message posted via http://www.accessmonster.com
.

[KenSheridan via AccessMonster.com]

Yep:

SELECT tblRegister.TransID, tblRegister.TransDate, tblRegister.TransNum,
tblRegister.TransMemo, tblRegister.Payment, tblRegister.Clr,
tblRegister.Deposit,
Format(DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","Account_ID = " &
[Forms]![Register]![cmbAccount] & " AND TransDate <=
" & Format(TransDate,"\#yyyy-mm-dd\#") & "
AND (TransID <= " & TransID & " OR TransDate <>
" & Format(TransDate,"\#yyyy-mm-dd\#") & ")"),"$#,##0.00;($#,##0.00)") AS
Balance
FROM tblRegister
WHERE Account_ID = [Forms]![Register]![cmbAccount]
ORDER BY tblRegister.TransDate, IIf(Deposit>Payment,0,1), tblRegister.
TransID;

This will put negative balances in parentheses I the conventional way. Or if
the query is being used as the basis for a report or form just format the
relevant control in the report or form.

Ken Sheridan
Stafford, England

Post by Silvio
Thanks Ken, that works. Is there a way to format the Balance as Currency
(e.g. $ 15,512.15)?
[quoted text clipped - 18 lines]

Post by KenSheridan via AccessMonster.com
Ken Sheridan
Stafford, England

[unknown]

I am trying to find a balance on hand using the # of items in plus that which I have received minus the # of item going out.



Silvio wrote:

Running Balance in Access 2007
04-Mar-10

Hello I have the following query with 3 columns

SELECT tblRegister.TransID, tblRegister.Payment, tblRegister.Deposi
FROM tblRegister

How can I have a running balance? Both columns, Payment and Deposit ar
positives numbers and each raw will always have either or (never both)
Payments will be subtracted from the running balance
This is what I would like to have

TransID Deposit Payment Balanc
1 1,000 1,00
2 600 40
3 10 41
4 30 38

And so forth... Also, I need the query to be updatable, I need to be abl
to place a checkmark in the Reconcile Check Box. Can it be done?

Previous Posts In This Thread:

On Thursday, March 04, 2010 4:23 PM
Silvio wrote:

Running Balance in Access 2007
Hello I have the following query with 3 columns

SELECT tblRegister.TransID, tblRegister.Payment, tblRegister.Deposi
FROM tblRegister

How can I have a running balance? Both columns, Payment and Deposit ar
positives numbers and each raw will always have either or (never both)
Payments will be subtracted from the running balance
This is what I would like to have

TransID Deposit Payment Balanc
1 1,000 1,00
2 600 40
3 10 41
4 30 38

And so forth... Also, I need the query to be updatable, I need to be abl
to place a checkmark in the Reconcile Check Box. Can it be done?

On Thursday, March 04, 2010 4:32 PM
Gina Whipp wrote:

Silvo,Add this to your query (on the *Field* line of your last column)...
Silvo

Add this to your query (on the *Field* line of your last column)..

RunSum: DSum("Deposit","tblRegister","TransID <= "
[TransID])-DSum("Payment","tblRegister","TransID <= " & [TransID]

However, I do not see where Reconciled is part of your query. Is it in th
same table as tblRegister

-
Gina Whip
2010 Microsoft MVP (Access

"I feel I have been denied critical, need to know, information!" - Tremor
I

http://www.regina-whipp.com/index_files/TipList.ht

Hello I have the following query with 3 columns

SELECT tblRegister.TransID, tblRegister.Payment, tblRegister.Deposi
FROM tblRegister

How can I have a running balance? Both columns, Payment and Deposit ar
positives numbers and each raw will always have either or (never both)
Payments will be subtracted from the running balance
This is what I would like to have

TransID Deposit Payment Balanc
1 1,000 1,00
2 600 40
3 10 41
4 30 38

And so forth... Also, I need the query to be updatable, I need to be abl
to place a checkmark in the Reconcile Check Box. Can it be done?

On Thursday, March 04, 2010 11:51 PM
KenSheridan via AccessMonster.com wrote:

Usually with this sort of 'statement' query a transaction date would be usedto
Usually with this sort of 'statement' query a transaction date would be use
to order the rows and compute the balances rather than the TransactionI
number. This caters for transactions not necessarily being entered in orde
of occurrence, and allows for transactions on the same day to be ordered b
credits first, though this traditional practice, which was originall
intended to prevent an account going into debit where the daily closin
balance was in credit, tends not to be followed these days, which of cours
works to the bank's advantage, not the customer's

Here is an example of an updatable query which does this, returning the row
in date order, with credits on the same date shown before debits. Within
set of credits or debits on the same day rows are sorted arbitrarily by
TransactionID:

SELECT TransactionDate, Credit, Debit,
DSUM("Nz(Credit,0)-Nz(Debit,0)","Transactions","TransactionDate <=

" & FORMAT(TransactionDate,"\#yyyy-mm-dd\#") & "
AND (TransactionID <= " & TransactionID & " OR TransactionDate <>
" & FORMAT(TransactionDate,"\#yyyy-mm-dd\#") & ")") AS Balance
FROM Transactions
ORDER BY TransactionDate, IIf(Credit>Debit,0,1), TransactionID;

Note that the DCount function is called only once per row by summing the
credit minus debit per transaction, using the Nz function to return a zero
for Null debits or credits..

Ken Sheridan
Stafford, England

Silvio wrote:

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1

On Friday, March 05, 2010 8:40 AM
Silvio wrote:

Thank you Ken this is exactly what I needed.
Thank you Ken this is exactly what I needed. However, I have hard time to
format the balance as Currency. Can you help?



"KenSheridan via AccessMonster.com" wrote:

On Friday, March 05, 2010 9:58 AM
Silvio wrote:

Ken, I also noticed that if I filter data (e.g.
Ken, I also noticed that if I filter data (e.g. Saving or Checking) the
running balance is wrong any idea why is that? Below is my query with your
code bulti in:

SELECT tblRegister.TransID, tblRegister.TransDate, tblRegister.TransNum,
tblRegister.TransMemo, tblRegister.Payment, tblRegister.Clr,
tblRegister.Deposit,
DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","TransDate <=

" & Format(TransDate,"\#yyyy-mm-dd\#") & "
AND (TransID <= " & TransID & " OR TransDate <>
" & Format(TransDate,"\#yyyy-mm-dd\#") & ")") AS Balance
FROM tblRegister
WHERE (((tblRegister.Account_ID)=[Forms]![Register]![cmbAccount]))
ORDER BY tblRegister.TransDate, IIf(Deposit>Payment,0,1), tblRegister.TransID;



"KenSheridan via AccessMonster.com" wrote:

On Friday, March 05, 2010 1:28 PM
Silvio wrote:

Thanks Ken, that works. Is there a way to format the Balance as Currency(e.g.
Thanks Ken, that works. Is there a way to format the Balance as Currency
(e.g. $ 15,512.15)?

"KenSheridan via AccessMonster.com" wrote:

On Friday, March 05, 2010 4:52 PM
KenSheridan via AccessMonster.com wrote:

That sounds as if you are using the same table for more than one account,which
That sounds as if you are using the same table for more than one account,
which is unusual. I would normally have separate tables for each of my
current (checking) and saving accounts. But using one table for all you would
need a column in the in the table to identify the account. I assume that the
AccountID performs this task, so you would need to build this into the DSum
function's criteria so that it references the cmbAccount control also:

SELECT tblRegister.TransID, tblRegister.TransDate, tblRegister.TransNum,
tblRegister.TransMemo, tblRegister.Payment, tblRegister.Clr,
tblRegister.Deposit,
DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","Account_ID) = " &
[Forms]![Register]![cmbAccount] & " AND TransDate <=
" & Format(TransDate,"\#yyyy-mm-dd\#") & "
AND (TransID <= " & TransID & " OR TransDate <>
" & Format(TransDate,"\#yyyy-mm-dd\#") & ")") AS Balance
FROM tblRegister
WHERE Account_ID = [Forms]![Register]![cmbAccount]
ORDER BY tblRegister.TransDate, IIf(Deposit>Payment,0,1), tblRegister.
TransID;

BTW if you see any blank lines in the above that is purely the result of the
newsreader breaking a line. You can remove the blank line from the query.

Ken Sheridan
Stafford, England

Silvio wrote:

--
Message posted via http://www.accessmonster.com

On Friday, March 05, 2010 4:56 PM
KenSheridan via AccessMonster.com wrote:

Oops, an extra parenthesis crept in. It should have been:SELECT tblRegister.
Oops, an extra parenthesis crept in. It should have been:

SELECT tblRegister.TransID, tblRegister.TransDate, tblRegister.TransNum,
tblRegister.TransMemo, tblRegister.Payment, tblRegister.Clr,
tblRegister.Deposit,
DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","Account_ID = " &
[Forms]![Register]![cmbAccount] & " AND TransDate <=
" & Format(TransDate,"\#yyyy-mm-dd\#") & "
AND (TransID <= " & TransID & " OR TransDate <>
" & Format(TransDate,"\#yyyy-mm-dd\#") & ")") AS Balance
FROM tblRegister
WHERE Account_ID = [Forms]![Register]![cmbAccount]
ORDER BY tblRegister.TransDate, IIf(Deposit>Payment,0,1), tblRegister.
TransID;

I have assumed AccountID is a number data type. If its text amend the above:

DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","Account_ID = """ &
[Forms]![Register]![cmbAccount] & """ AND TransDate <=

Ken Sheridan
Stafford, England

--
Message posted via http://www.accessmonster.com

On Friday, March 05, 2010 7:06 PM
KenSheridan via AccessMonster.com wrote:

Yep:SELECT tblRegister.TransID, tblRegister.TransDate, tblRegister.
Yep:

SELECT tblRegister.TransID, tblRegister.TransDate, tblRegister.TransNum,
tblRegister.TransMemo, tblRegister.Payment, tblRegister.Clr,
tblRegister.Deposit,
Format(DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","Account_ID = " &
[Forms]![Register]![cmbAccount] & " AND TransDate <=
" & Format(TransDate,"\#yyyy-mm-dd\#") & "
AND (TransID <= " & TransID & " OR TransDate <>
" & Format(TransDate,"\#yyyy-mm-dd\#") & ")"),"$#,##0.00;($#,##0.00)") AS
Balance
FROM tblRegister
WHERE Account_ID = [Forms]![Register]![cmbAccount]
ORDER BY tblRegister.TransDate, IIf(Deposit>Payment,0,1), tblRegister.
TransID;

This will put negative balances in parentheses I the conventional way. Or if
the query is being used as the basis for a report or form just format the
relevant control in the report or form.

Ken Sheridan
Stafford, England

Silvio wrote:

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1


Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk: Conditional looping incorporating the Greater Than functoid.
http://www.eggheadcafe.com/tutorials/aspnet/e4334816-d106-40f2-812d-043c18df964c/biztalk-conditional-loop.aspx

[KenSheridan via AccessMonster.com]

Please post details of the relevant table(s), including the field names and
their data types.

Ken Sheridan
Stafford, England

Post by unknown
I am trying to find a balance on hand using the # of items in plus that which I have received minus the # of item going out.
Running Balance in Access 2007
04-Mar-10
Hello I have the following query with 3 columns
SELECT tblRegister.TransID, tblRegister.Payment, tblRegister.Deposi
FROM tblRegister
How can I have a running balance? Both columns, Payment and Deposit ar
positives numbers and each raw will always have either or (never both)
Payments will be subtracted from the running balance
This is what I would like to have
TransID Deposit Payment Balanc
1 1,000 1,00
2 600 40
3 10 41
4 30 38
And so forth... Also, I need the query to be updatable, I need to be abl
to place a checkmark in the Reconcile Check Box. Can it be done?
Running Balance in Access 2007
Hello I have the following query with 3 columns
SELECT tblRegister.TransID, tblRegister.Payment, tblRegister.Deposi
FROM tblRegister
How can I have a running balance? Both columns, Payment and Deposit ar
positives numbers and each raw will always have either or (never both)
Payments will be subtracted from the running balance
This is what I would like to have
TransID Deposit Payment Balanc
1 1,000 1,00
2 600 40
3 10 41
4 30 38
And so forth... Also, I need the query to be updatable, I need to be abl
to place a checkmark in the Reconcile Check Box. Can it be done?
On Thursday, March 04, 2010 4:32 PM
Silvo,Add this to your query (on the *Field* line of your last column)...
Silvo
Add this to your query (on the *Field* line of your last column)..
RunSum: DSum("Deposit","tblRegister","TransID <= "
[TransID])-DSum("Payment","tblRegister","TransID <= " & [TransID]
However, I do not see where Reconciled is part of your query. Is it in th
same table as tblRegister
-
Gina Whip
2010 Microsoft MVP (Access
"I feel I have been denied critical, need to know, information!" - Tremor
I
http://www.regina-whipp.com/index_files/TipList.ht
Hello I have the following query with 3 columns
SELECT tblRegister.TransID, tblRegister.Payment, tblRegister.Deposi
FROM tblRegister
How can I have a running balance? Both columns, Payment and Deposit ar
positives numbers and each raw will always have either or (never both)
Payments will be subtracted from the running balance
This is what I would like to have
TransID Deposit Payment Balanc
1 1,000 1,00
2 600 40
3 10 41
4 30 38
And so forth... Also, I need the query to be updatable, I need to be abl
to place a checkmark in the Reconcile Check Box. Can it be done?
On Thursday, March 04, 2010 11:51 PM
Usually with this sort of 'statement' query a transaction date would be usedto
Usually with this sort of 'statement' query a transaction date would be use
to order the rows and compute the balances rather than the TransactionI
number. This caters for transactions not necessarily being entered in orde
of occurrence, and allows for transactions on the same day to be ordered b
credits first, though this traditional practice, which was originall
intended to prevent an account going into debit where the daily closin
balance was in credit, tends not to be followed these days, which of cours
works to the bank's advantage, not the customer's
Here is an example of an updatable query which does this, returning the row
in date order, with credits on the same date shown before debits. Within
set of credits or debits on the same day rows are sorted arbitrarily by
SELECT TransactionDate, Credit, Debit,
DSUM("Nz(Credit,0)-Nz(Debit,0)","Transactions","TransactionDate <=
" & FORMAT(TransactionDate,"\#yyyy-mm-dd\#") & "
AND (TransactionID <= " & TransactionID & " OR TransactionDate <>
" & FORMAT(TransactionDate,"\#yyyy-mm-dd\#") & ")") AS Balance
FROM Transactions
ORDER BY TransactionDate, IIf(Credit>Debit,0,1), TransactionID;
Note that the DCount function is called only once per row by summing the
credit minus debit per transaction, using the Nz function to return a zero
for Null debits or credits..
Ken Sheridan
Stafford, England
Thank you Ken this is exactly what I needed.
Thank you Ken this is exactly what I needed. However, I have hard time to
format the balance as Currency. Can you help?
Ken, I also noticed that if I filter data (e.g.
Ken, I also noticed that if I filter data (e.g. Saving or Checking) the
running balance is wrong any idea why is that? Below is my query with your
SELECT tblRegister.TransID, tblRegister.TransDate, tblRegister.TransNum,
tblRegister.TransMemo, tblRegister.Payment, tblRegister.Clr,
tblRegister.Deposit,
DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","TransDate <=
" & Format(TransDate,"\#yyyy-mm-dd\#") & "
AND (TransID <= " & TransID & " OR TransDate <>
" & Format(TransDate,"\#yyyy-mm-dd\#") & ")") AS Balance
FROM tblRegister
WHERE (((tblRegister.Account_ID)=[Forms]![Register]![cmbAccount]))
ORDER BY tblRegister.TransDate, IIf(Deposit>Payment,0,1), tblRegister.TransID;
Thanks Ken, that works. Is there a way to format the Balance as Currency(e.g.
Thanks Ken, that works. Is there a way to format the Balance as Currency
(e.g. $ 15,512.15)?
On Friday, March 05, 2010 4:52 PM
That sounds as if you are using the same table for more than one account,which
That sounds as if you are using the same table for more than one account,
which is unusual. I would normally have separate tables for each of my
current (checking) and saving accounts. But using one table for all you would
need a column in the in the table to identify the account. I assume that the
AccountID performs this task, so you would need to build this into the DSum
SELECT tblRegister.TransID, tblRegister.TransDate, tblRegister.TransNum,
tblRegister.TransMemo, tblRegister.Payment, tblRegister.Clr,
tblRegister.Deposit,
DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","Account_ID) = " &
[Forms]![Register]![cmbAccount] & " AND TransDate <=
" & Format(TransDate,"\#yyyy-mm-dd\#") & "
AND (TransID <= " & TransID & " OR TransDate <>
" & Format(TransDate,"\#yyyy-mm-dd\#") & ")") AS Balance
FROM tblRegister
WHERE Account_ID = [Forms]![Register]![cmbAccount]
ORDER BY tblRegister.TransDate, IIf(Deposit>Payment,0,1), tblRegister.
TransID;
BTW if you see any blank lines in the above that is purely the result of the
newsreader breaking a line. You can remove the blank line from the query.
Ken Sheridan
Stafford, England
On Friday, March 05, 2010 4:56 PM
Oops, an extra parenthesis crept in. It should have been:SELECT tblRegister.
SELECT tblRegister.TransID, tblRegister.TransDate, tblRegister.TransNum,
tblRegister.TransMemo, tblRegister.Payment, tblRegister.Clr,
tblRegister.Deposit,
DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","Account_ID = " &
[Forms]![Register]![cmbAccount] & " AND TransDate <=
" & Format(TransDate,"\#yyyy-mm-dd\#") & "
AND (TransID <= " & TransID & " OR TransDate <>
" & Format(TransDate,"\#yyyy-mm-dd\#") & ")") AS Balance
FROM tblRegister
WHERE Account_ID = [Forms]![Register]![cmbAccount]
ORDER BY tblRegister.TransDate, IIf(Deposit>Payment,0,1), tblRegister.
TransID;
DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","Account_ID = """ &
[Forms]![Register]![cmbAccount] & """ AND TransDate <=
Ken Sheridan
Stafford, England
On Friday, March 05, 2010 7:06 PM
Yep:SELECT tblRegister.TransID, tblRegister.TransDate, tblRegister.
SELECT tblRegister.TransID, tblRegister.TransDate, tblRegister.TransNum,
tblRegister.TransMemo, tblRegister.Payment, tblRegister.Clr,
tblRegister.Deposit,
Format(DSum("Nz(Deposit,0)-Nz(Payment,0)","tblRegister","Account_ID = " &
[Forms]![Register]![cmbAccount] & " AND TransDate <=
" & Format(TransDate,"\#yyyy-mm-dd\#") & "
AND (TransID <= " & TransID & " OR TransDate <>
" & Format(TransDate,"\#yyyy-mm-dd\#") & ")"),"$#,##0.00;($#,##0.00)") AS
Balance
FROM tblRegister
WHERE Account_ID = [Forms]![Register]![cmbAccount]
ORDER BY tblRegister.TransDate, IIf(Deposit>Payment,0,1), tblRegister.
TransID;
This will put negative balances in parentheses I the conventional way. Or if
the query is being used as the basis for a report or form just format the
relevant control in the report or form.
Ken Sheridan
Stafford, England
Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk: Conditional looping incorporating the Greater Than functoid.
http://www.eggheadcafe.com/tutorials/aspnet/e4334816-d106-40f2-812d-043c18df964c/biztalk-conditional-loop.aspx