Help with running balance

I need help on running balance for my banking DB.

I have a Table (Transactions) where I store all account transactions,
with the fields:
TransID (AutoNumber)
TransValue (Currency for credits and debits)
TransDate (ShortDate)

I set a query based on that table with all the fields, and another
field (CurrBalance) where I use the formula that I saw in one of this
threads:
CurrBalance:Format(DSum("TransValue";"Transactions ";"TransDate=" &
Format([TransDate];"\#dd\/mm\/yyyy\#");"#,###.00 €")

The query is sorted by TransDate. Everything works fine if TransDate
is different, but not working with transactions on the same date. I
don't know if I can use TransID and how to use it to have a running
balance for each transaction sorted by date and working with several
transactions on same date.

Any help would be appreciated
Johnny

Johnny,

You can't use date for the very reason you stated. Place the below in a
query on the Field: line.

RunSum: DSum("TransValue","Transactions","TransID = " &
[TransID])-DSum("apPayment","Transactions","TransID = " & [TransID])

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"Johnny" wrote in message
...
I need help on running balance for my banking DB.

I have a Table (Transactions) where I store all account transactions,
with the fields:
TransID (AutoNumber)
TransValue (Currency for credits and debits)
TransDate (ShortDate)

I set a query based on that table with all the fields, and another
field (CurrBalance) where I use the formula that I saw in one of this
threads:
CurrBalance:Format(DSum("TransValue";"Transactions ";"TransDate=" &
Format([TransDate];"\#dd\/mm\/yyyy\#");"#,###.00 €")

The query is sorted by TransDate. Everything works fine if TransDate
is different, but not working with transactions on the same date. I
don't know if I can use TransID and how to use it to have a running
balance for each transaction sorted by date and working with several
transactions on same date.

Any help would be appreciated
Johnny

Johnny:

You need to bring the TransID as well as the TransDate into play to
distinguish between transactions on the same date:

SELECT TransDate, TransValue,
(SELECT SUM(TransValue)
FROM Transactions AS T2
WHERE T2.TransDate = T1.TransDate
AND ( T2.TransactionID = T1.TransactionID
OR T2.TransDate T1.TransDate)) AS Balance
FROM Transactions AS T1
ORDER BY TransDate DESC , TransactionID DESC;

Note that the order of transactions within a day will be arbitrary as (a) an
autonumber column only guarantees uniqueness not sequence and should not be
relied upon for such, and (b) the transactions might not necessarily have
been entered into the table in real time so the TransID might not in any case
reflect the order of the transactions.

The above query will not be updatable as it uses a subquery. This is fine
if it’s the basis of a report. If you need an updatable query as the basis
for a form then call the DSum function instead of the subquery, with similar
expressions as its criteria.

Ken Sheridan
Stafford, England

"Johnny" wrote:

I need help on running balance for my banking DB.

I have a Table (Transactions) where I store all account transactions,
with the fields:
TransID (AutoNumber)
TransValue (Currency for TransValues and debits)
TransDate (ShortDate)

I set a query based on that table with all the fields, and another
field (CurrBalance) where I use the formula that I saw in one of this
threads:
CurrBalance:Format(DSum("TransValue";"Transactions ";"TransDate=" &
Format([TransDate];"\#dd\/mm\/yyyy\#");"#,###.00 €")

The query is sorted by TransDate. Everything works fine if TransDate
is different, but not working with transactions on the same date. I
don't know if I can use TransID and how to use it to have a running
balance for each transaction sorted by date and working with several
transactions on same date.

Any help would be appreciated
Johnny

Thank you verymuch Ken and Gina I'll give it a try!

Johnny

Hi again Ken

In fact the purpose is for an updatable query as the basis for a form.
Can you give some advise on how to call the DSum function instead of
the subquery?
I'm quite newbe here.
Thanks in advance
Johnny

Johnny wrote:
Hi again Ken

In fact the purpose is for an updatable query as the basis for a form.
Can you give some advise on how to call the DSum function instead of
the subquery?
I'm quite newbe here.
Thanks in advance
Johnny

Well, you should start with online help, which I believe has a couple of
examples.
Failing that, there appear to be several examples in these google search
results.
http://www.google.com/search?rlz=1C1...+query+example

After utilizing these resources, if somethin is still beyond you, come back
and let us know specifically what is puzzling you. :-)

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Johnny:

Try this:

SELECT TransDate, TransValue,
DSum("TransValue", "Transactions",
"TransDate = #" & Format(T1.TransDate, "yyyy-mm-dd") &
"# (And TransactionID = " & T1.TransactionID &
" Or TransDate #" & Format(T1.TransDate, "yyyy-mm-dd") & "#)")
AS Balance
FROM Transactions AS T1
ORDER BY TransDate DESC , TransactionID DESC;

The reason for calling the Format function is that date literals in Access
have to be either in US short date format or an internationally unambiguous
format. YYYY-MM-DD is the ISO standard for date formatting, so will be
understood whatever the regional date format settings on the system.

Ken Sheridan
Stafford, England

"Johnny" wrote:

Hi again Ken

In fact the purpose is for an updatable query as the basis for a form.
Can you give some advise on how to call the DSum function instead of
the subquery?
I'm quite newbe here.
Thanks in advance
Johnny

Hello Ken

Thank you for your answer, I'll try your sugestion!

Johnny

Hello Ken,

I tried what you sugested but the filed Balance result is #Error:
Sintax error (missing operator) on expression 'TransDate =
#2008-26-05# (And TransactionID = 1373 Or TransDate
#2008-06-25#)'.
I can´t figure out what could be wrong, sorry boring you but I would
appreciate your help.

Thanks in advance

Johnny

Johnny wrote:
Hello Ken,

I tried what you sugested but the filed Balance result is #Error:
Sintax error (missing operator) on expression 'TransDate =
#2008-26-05# (And TransactionID = 1373 Or TransDate
#2008-06-25#)'.
I can´t figure out what could be wrong, sorry boring you but I would
appreciate your help.

Thanks in advance

That opening parenthesis is misplaced. Try this:
TransDate =
#2008-26-05# And (TransactionID = 1373 Or TransDate
#2008-06-25#)

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"