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/tutorial...2d-043c18df964c/biztalk-conditional-loop.aspx
