Running Balance in Access 2007


S

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?
 
Ad

Advertisements

G

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

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?
 
K

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
 
S

Silvio

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

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;
 
K

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
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;


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]
 
Ad

Advertisements

K

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
 
S

Silvio

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

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
Thanks Ken, that works. Is there a way to format the Balance as Currency
(e.g. $ 15,512.15)?
Oops, an extra parenthesis crept in. It should have been:
[quoted text clipped - 18 lines]
Ken Sheridan
Stafford, England
 
L

Leon Garrett

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:

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?

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?

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:

--



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
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;



:

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)?

:

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:

--


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

--


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:

--




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
 
K

KenSheridan via AccessMonster.com

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

Ken Sheridan
Stafford, England

Leon said:
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?

Previous Posts In This Thread:

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?

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?

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

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
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;

:

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)?

:

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

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

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

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
 
Ad

Advertisements

J

jk-can

Gina Whipp wrote on 03/04/2010 16:32 ET
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top