Help with running balance

J

Johnny

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
 
G

Gina Whipp

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

Ken Sheridan

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
 
J

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
 
B

Bob Barrows

Johnny said:
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
Click to expand...

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=1C...me&ie=UTF-8&q=calling+dsum+in+a+query+example

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

Ken Sheridan

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
 
J

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
 
B

Bob Barrows

Johnny said:
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
Click to expand...
That opening parenthesis is misplaced. Try this:
TransDate <=
#2008-26-05# And (TransactionID <= 1373 Or TransDate <>
#2008-06-25#)
 
J

Johnny

Hi Ken,

Once more I request your help!
I used the sugestion you gave me and with Bob Barrows help I could
make it work!
That way, the query returns the Balance of all transactions in
Transactions table. What I need is that the query return all all
transactions in Transactions table for a specific person (ClientID).

Could the following be a solution??
SELECT TransDate, TransValue, ClientID,
DSum("TransValue", "Transactions",
"TransDate <= #" & Format(T1.TransDate, "yyyy-mm-dd") &
"# And (TransactionID <= " & T1.TransactionID &
" Or TransDate <> #" & Format(T1.TransDate, "yyyy-mm-dd") & "#)
And T1.ClientID = [Forms]![MyForm]![MyControl]")
AS Balance
FROM Transactions AS T1
ORDER BY TransDate DESC , TransactionID DESC;

[Forms]![MyForm]![MyControl] is a combo box returning a number!

Thanks once more for the suport
Johnny
 
J

John Spencer (MVP)

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

If you are trying to get all this for one specific client, you might change
the query to

SELECT TransDate, TransValue, ClientID,
DSum("TransValue", "Transactions",
"TransDate <= #" & Format(T1.TransDate, "yyyy-mm-dd") &
"# And (TransactionID <= " & T1.TransactionID &
" Or TransDate <> #" & Format(T1.TransDate, "yyyy-mm-dd") & "#)
And ClientID =" & T1.ClientID) AS Balance
FROM Transactions AS T1
WHERE T1.ClientID = [Forms]![MyForm]![MyControl]
ORDER BY TransDate DESC , TransactionID DESC;

Hopefully all the needed parentheses are in the correct place.

Alternative would be to use a subquery to get the Balance
SELECT TransDate, TransValue, ClientID
, (SELECT SUM(TransValue) FROM Transactions as T
WHERE T.ClientID = T1.ClientID
AND (T.TransDate <= T1.TransDate
AND T.TransactionID<=T1.TransactionID
Or T.TransDate <> T1.TransDate) As Balance
FROM Transactions AS T1
WHERE T1.ClientID = [Forms]![MyForm]![MyControl]
ORDER BY TransDate DESC , TransactionID DESC;



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
J

Johnny

Hi John,

I used what you sugested:

SELECT TransDate, TransValue, ClientID,
DSum("TransValue", "Transactions",
"TransDate <= #" & Format(T1.TransDate, "yyyy-mm-dd") &
"# And (TransactionID <= " & T1.TransactionID &
" Or TransDate <> #" & Format(T1.TransDate, "yyyy-mm-dd") & "#)
And ClientID =" & T1.ClientID) AS Balance
FROM Transactions AS T1
WHERE T1.ClientID = [Forms]![MyForm]![MyControl]
ORDER BY TransDate DESC , TransactionID DESC;

and it worked like a charm!
Thank you very much for your suport
Johnny
 
M

Michael Strite

This thread has been very helpful to me. However, I have a slightly different need. Instead of feeding the specific ClientID from a separate form, I just need the query to have the running balance by date but tracked per each ClientID. Is there some small modification to this that would make that work?

For example

Date ClientID Transvalue Balance
01/01/09 client1 23 23
01/02/09 client2 12 12
01/06/09 client1 05 28
01/06/09 client3 09 09
01/08/09 client3 14 23
01/20/09 client1 04 32




Johnny wrote:

Re: Help with running balance
10-Jan-09

Hi John

I used what you sugested

SELECT TransDate, TransValue, ClientID
DSum("TransValue", "Transactions"
"TransDate <= #" & Format(T1.TransDate, "yyyy-mm-dd")
"# And (TransactionID <= " & T1.TransactionID
" Or TransDate <> #" & Format(T1.TransDate, "yyyy-mm-dd") & "#
And ClientID =" & T1.ClientID) AS Balanc
FROM Transactions AS T
WHERE T1.ClientID = [Forms]![MyForm]![MyControl
ORDER BY TransDate DESC , TransactionID DESC

and it worked like a charm
Thank you very much for your supor
Johnny

EggHeadCafe - Software Developer Portal of Choice
Speed Up Sql Server Data Inserts With SqlBulkCopy
http://www.eggheadcafe.com/tutorial...18-cee8a2357251/speed-up-sql-server-data.aspx
 
M

Michael Strite

This thread has been very helpful to me. However, I have a slightly different need. Instead of feeding the specific ClientID from a separate form, I just need the query to have the running balance by date but tracked per each ClientID. Is there some small modification to this that would make that work?

For example

Date ClientID Transvalue Balance
01/01/09 client1 23 23
01/02/09 client2 12 12
01/06/09 client1 05 28
01/06/09 client3 09 09
01/08/09 client3 14 23
01/20/09 client1 04 32




Johnny wrote:

Re: Help with running balance
10-Jan-09

Hi John

I used what you sugested

SELECT TransDate, TransValue, ClientID
DSum("TransValue", "Transactions"
"TransDate <= #" & Format(T1.TransDate, "yyyy-mm-dd")
"# And (TransactionID <= " & T1.TransactionID
" Or TransDate <> #" & Format(T1.TransDate, "yyyy-mm-dd") & "#
And ClientID =" & T1.ClientID) AS Balanc
FROM Transactions AS T
WHERE T1.ClientID = [Forms]![MyForm]![MyControl
ORDER BY TransDate DESC , TransactionID DESC

and it worked like a charm
Thank you very much for your supor
Johnny

EggHeadCafe - Software Developer Portal of Choice
ASP.NET: Hamburger, Yes! AJAX, "NOT!", Redux!
http://www.eggheadcafe.com/tutorial...9-934d-edb57ab852e3/aspnet-hamburger-yes.aspx
 
M

Michael Strite

This thread has been very helpful to me. However, I have a slightly different need. Instead of feeding the specific ClientID from a separate form, I just need the query to have the running balance by date but tracked per each ClientID. Is there some small modification to this that would make that work?

For example

Date ClientID Transvalue Balance
01/01/09 client1 23 23
01/02/09 client2 12 12
01/06/09 client1 05 28
01/06/09 client3 09 09
01/08/09 client3 14 23
01/20/09 client1 04 32



Johnny wrote:

Re: Help with running balance
10-Jan-09

Hi John

I used what you sugested

SELECT TransDate, TransValue, ClientID
DSum("TransValue", "Transactions"
"TransDate <= #" & Format(T1.TransDate, "yyyy-mm-dd")
"# And (TransactionID <= " & T1.TransactionID
" Or TransDate <> #" & Format(T1.TransDate, "yyyy-mm-dd") & "#
And ClientID =" & T1.ClientID) AS Balanc
FROM Transactions AS T
WHERE T1.ClientID = [Forms]![MyForm]![MyControl
ORDER BY TransDate DESC , TransactionID DESC

and it worked like a charm
Thank you very much for your supor
Johnny

EggHeadCafe - Software Developer Portal of Choice
Moving to VB.NET - Appelman [aPress]
http://www.eggheadcafe.com/tutorial...195-c85d26d24e78/moving-to-vbnet--appelm.aspx
 
V

vanderghast

Using joins:


SELECT a.date, a.clientID, LAST(a.transvalue), SUM(b.transvalue)
FROM tableName As a INNER JOIN tableName AS b
ON a.clientID=b.clientID AND a.date>= b.date
GROUP BY a.date, a.clientID

ORDER BY a.clientID, a.date


The Order by clause is optional, and can be used to help to validate the
result.


Vanderghast, Access MVP



in message news:[email protected]...
 
I

inept_webmaster

Many thanks Marshall & vanderghast for your attempts to help a
newbie... I hesitate to bother you but I seem to be stuck again. :)
When I run my query with the following query field included, it gives
me a syntax error. I am sure that I am missing some critical
paretheses but for the life of me I cannot seem to figure out where to
put them. I am still trying to figure out the logic behind these SQL
queries... Forgive my ignorance but can you tell me what I need to
change with the following statement.


Balance: SELECT a.TransDate, a.CustID, LAST(a.ChargeAmount), SUM
(b.ChargeAmount)
FROM Accounts As a INNER JOIN Accounts AS b
ON a.CustID=b.CustID AND a.TransDate>= b.TransDate
GROUP BY a.TransDate, a.CustID
ORDER BY a.CustID, a.TransDate
 

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

Running Sum 4
Date parameter queries 2
query to calc running total 1
daily interest calculation 3
Query Aggregation Problem 2
How do I query a calculated control? 1
DateAdd Function in Queries 2
Running Balance in Access 2007 5

Top