How to get a running balance total

[CJM3407]

Ok, here is my situation. I have a table where you can enter deposits and
withdrawals for an account involved in a specific investment. Then I do a
query that returns a 30 day period report. I use Left Outer Join to make the
query, enter some data every day in the period (even if it is just 0s). So,
from june 1 to june 30. There are 30 entries and where a withdrawal or
deposit is made that data is shown. My problem is I need a running balance
total. So day one you invest 30 dollars. It should say $30 in the balance
column the whole way down until you get to another activity. So a withdrawal
of $10, would make it go to $20 for the rest of the way, etc. I use
NewBalance:
DSUM([Deposits]-[Withdrawals], "tblAccountActivity")

1. I dont know what to use in the second parameter, tblAccountAct, holds all
Activity across all Groups and investment plans.
2. What it comes up with, is it puts in the balance column the change
multiplied by the number of entries in that table. and then any time there is
no activity it goes back to saying 0.

 

[vanderghast]

You need to bring two references (alias) to your table.

Graphically, bring the table, twice, one will get an _1 appended to its
name. Join them through their accountID field (you want a running sum by
accountID, not across ALL the accounts, after all, I assume). Change the
query to a Total query. Bring the accountID, the date and the amount from
the first table into the grid. Keep the proposed GROUP BY. Bring the amount
from the _1 table in the grid, change the GROUP BY to SUM. Bring the date
from the _1 table, change the GROUP BY to WHERE, and, in the criteria line,
add the criteria:

<= [tablename].[theDateField]


where you use the real table name and the real date field name (without _1,
but keep the two pairs of brackets).


That's all. The SUM should give the running required sum.

You can change the GROUP BY to LAST under the date field you dragged from
the first reference to your table, if you want, that may makes the query
running a little bit faster (but probably not noticeable).

I assume you have no more than ONE record per accound, per date (date_time).
If you have multiple transactions per date, per account, use a different
TIME for each transaction, for that account, so the uniqueness of the pair
(Account, Date_time) is kept.



Vanderghast, Access MVP

 

[Allen Browne]

You could use an expression such as this:
DSum("[Deposits]-[Withdrawals]", "tblAccountActivity",
"([AccountID] = " & Nz([AccountID], 0) & ") AND ([ActivityDateTime] < " &
Format(Nz([ActivityDateTime], Now()), "\#mm\/dd\/yyy hh:nn:ss\#") & ")")

This assumes:
- a field named AccountID (type Number),
- a field named ActivityDateTime (type Date/Time),
- the combination of these 2 fields is unique.
The 3rd requirement is necessary: otherwise the concept of the 'previous'
activity on an account is undefined.

If you don't mind a read-only result, a subquery might be quicker. More
info:
http://allenbrowne.com/subquery-01.html#AnotherRecord

An easier solution is to do it in a report. You can add a text box to your
report, and give it properties like this:
Control Source =[Deposits] - [Withdrawals]
Running Sum Over Group
Assuming you grouped on the AccountID, that should work.

 

[CJM3407]

Ok, let me be more specific, because I am new to Access and that didnt make a
huge amount of sense. I have three criteria my data has to meet. Two of
those criteria are checked in a qry called qryTermReportData. It basically
filters out the data and returns the same data table, but with the
requirement that it matches the CoopNo and the Investment ID. Then a
different qry qryTermReport. Takes that qry along with a DateTable(Contains
all the dates for the next 50 years) and left outer joins the tables. This
gives me the 30 day schema where everyday is represented no matter if it has
activity linked to it.

My current attempt, then does a ActDeposits: IIF([Deposits]>0, [Deposits],0)
and same for withdrawals. which fills in the null data (from the join) with
0s. Then I do
Change: Sum([ActDeposits]-[ActWithdrawals]). And finally
NewBalance: DSum ( [Change], "qryTermReport"). (As you can see the domain
is referencing the same qry it is in, but i dont know if that is correct.)
That is resulting in a 30 times now (with 3 entries.)

You need to bring two references (alias) to your table.

Graphically, bring the table, twice, one will get an _1 appended to its
name. Join them through their accountID field (you want a running sum by
accountID, not across ALL the accounts, after all, I assume). Change the
query to a Total query. Bring the accountID, the date and the amount from
the first table into the grid. Keep the proposed GROUP BY. Bring the amount
from the _1 table in the grid, change the GROUP BY to SUM. Bring the date
from the _1 table, change the GROUP BY to WHERE, and, in the criteria line,
add the criteria:

<= [tablename].[theDateField]


where you use the real table name and the real date field name (without _1,
but keep the two pairs of brackets).


That's all. The SUM should give the running required sum.

You can change the GROUP BY to LAST under the date field you dragged from
the first reference to your table, if you want, that may makes the query
running a little bit faster (but probably not noticeable).

I assume you have no more than ONE record per accound, per date (date_time).
If you have multiple transactions per date, per account, use a different
TIME for each transaction, for that account, so the uniqueness of the pair
(Account, Date_time) is kept.



Vanderghast, Access MVP

Ok, here is my situation. I have a table where you can enter deposits and
withdrawals for an account involved in a specific investment. Then I do a
query that returns a 30 day period report. I use Left Outer Join to make
the
query, enter some data every day in the period (even if it is just 0s).
So,
from june 1 to june 30. There are 30 entries and where a withdrawal or
deposit is made that data is shown. My problem is I need a running
balance
total. So day one you invest 30 dollars. It should say $30 in the
balance
column the whole way down until you get to another activity. So a
withdrawal
of $10, would make it go to $20 for the rest of the way, etc. I use
NewBalance:
DSUM([Deposits]-[Withdrawals], "tblAccountActivity")

1. I dont know what to use in the second parameter, tblAccountAct, holds
all
Activity across all Groups and investment plans.
2. What it comes up with, is it puts in the balance column the change
multiplied by the number of entries in that table. and then any time there
is
no activity it goes back to saying 0.

 

[vanderghast]

A domain function cannot refer to the query in which it is used, that would
be like a cyclic computation in Excel. You need another query which will
either use the Domain function over the saved query: either two fields,
ActDeposits and ActWithdrawals, either just one field, ActAmount (+ for
deposit, - for withdrawal).

Try to follow the instructions I supplied, but instead of to bring the
'table' twice, bring the 'saved query' with the account, date_time of
transactions, and ActAmount involved by the transaction (+ for a credit, -
for a debit).


Vanderghast, Access MVP

Ok, let me be more specific, because I am new to Access and that didnt
make a
huge amount of sense. I have three criteria my data has to meet. Two of
those criteria are checked in a qry called qryTermReportData. It
basically
filters out the data and returns the same data table, but with the
requirement that it matches the CoopNo and the Investment ID. Then a
different qry qryTermReport. Takes that qry along with a
DateTable(Contains
all the dates for the next 50 years) and left outer joins the tables.
This
gives me the 30 day schema where everyday is represented no matter if it
has
activity linked to it.

My current attempt, then does a ActDeposits: IIF([Deposits]>0,
[Deposits],0)
and same for withdrawals. which fills in the null data (from the join)
with
0s. Then I do
Change: Sum([ActDeposits]-[ActWithdrawals]). And finally
NewBalance: DSum ( [Change], "qryTermReport"). (As you can see the domain
is referencing the same qry it is in, but i dont know if that is correct.)
That is resulting in a 30 times now (with 3 entries.)

You need to bring two references (alias) to your table.

Graphically, bring the table, twice, one will get an _1 appended to its
name. Join them through their accountID field (you want a running sum by
accountID, not across ALL the accounts, after all, I assume). Change the
query to a Total query. Bring the accountID, the date and the amount from
the first table into the grid. Keep the proposed GROUP BY. Bring the
amount
from the _1 table in the grid, change the GROUP BY to SUM. Bring the date
from the _1 table, change the GROUP BY to WHERE, and, in the criteria
line,
add the criteria:

<= [tablename].[theDateField]


where you use the real table name and the real date field name (without
_1,
but keep the two pairs of brackets).


That's all. The SUM should give the running required sum.

You can change the GROUP BY to LAST under the date field you dragged from
the first reference to your table, if you want, that may makes the query
running a little bit faster (but probably not noticeable).

I assume you have no more than ONE record per accound, per date
(date_time).
If you have multiple transactions per date, per account, use a different
TIME for each transaction, for that account, so the uniqueness of the
pair
(Account, Date_time) is kept.



Vanderghast, Access MVP

Ok, here is my situation. I have a table where you can enter deposits
and
withdrawals for an account involved in a specific investment. Then I
do a
query that returns a 30 day period report. I use Left Outer Join to
make
the
query, enter some data every day in the period (even if it is just 0s).
So,
from june 1 to june 30. There are 30 entries and where a withdrawal or
deposit is made that data is shown. My problem is I need a running
balance
total. So day one you invest 30 dollars. It should say $30 in the
balance
column the whole way down until you get to another activity. So a
withdrawal
of $10, would make it go to $20 for the rest of the way, etc. I use
NewBalance:
DSUM([Deposits]-[Withdrawals], "tblAccountActivity")

1. I dont know what to use in the second parameter, tblAccountAct,
holds
all
Activity across all Groups and investment plans.
2. What it comes up with, is it puts in the balance column the change
multiplied by the number of entries in that table. and then any time
there
is
no activity it goes back to saying 0.

 

[CJM3407]

My Overall objective, is to do this only for the purpose of calculating
average Balance over 30 days, without doing the headache of VB code, that
does calculations based on date spans. At this moment I am mapping out that
code. My two options were from this info: 7/3/09 to 7/8/09 you had $10,000
from 7/9/09 to 7/25/09 you had 7,000, do one of these:
1. Have this qry work so it puts up the balances for all 30 days, so I can
do a quick add for a total balance for the month, and divide by 30 days.
2. Or doing it programatically and say, from the 3rd to the 8th is 5 days.
Multiply 5 times 10k, etc.

You could use an expression such as this:
DSum("[Deposits]-[Withdrawals]", "tblAccountActivity",
"([AccountID] = " & Nz([AccountID], 0) & ") AND ([ActivityDateTime] < " &
Format(Nz([ActivityDateTime], Now()), "\#mm\/dd\/yyy hh:nn:ss\#") & ")")

This assumes:
- a field named AccountID (type Number),
- a field named ActivityDateTime (type Date/Time),
- the combination of these 2 fields is unique.
The 3rd requirement is necessary: otherwise the concept of the 'previous'
activity on an account is undefined.

If you don't mind a read-only result, a subquery might be quicker. More
info:
http://allenbrowne.com/subquery-01.html#AnotherRecord

An easier solution is to do it in a report. You can add a text box to your
report, and give it properties like this:
Control Source =[Deposits] - [Withdrawals]
Running Sum Over Group
Assuming you grouped on the AccountID, that should work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Ok, here is my situation. I have a table where you can enter deposits and
withdrawals for an account involved in a specific investment. Then I do a
query that returns a 30 day period report. I use Left Outer Join to make
the
query, enter some data every day in the period (even if it is just 0s).
So,
from june 1 to june 30. There are 30 entries and where a withdrawal or
deposit is made that data is shown. My problem is I need a running
balance
total. So day one you invest 30 dollars. It should say $30 in the
balance
column the whole way down until you get to another activity. So a
withdrawal
of $10, would make it go to $20 for the rest of the way, etc. I use
NewBalance:
DSUM([Deposits]-[Withdrawals], "tblAccountActivity")

1. I dont know what to use in the second parameter, tblAccountAct, holds
all
Activity across all Groups and investment plans.
2. What it comes up with, is it puts in the balance column the change
multiplied by the number of entries in that table. and then any time there
is
no activity it goes back to saying 0.

 

[Allen Browne]

That's a much bigger question.

You won't have transactions for every day for every account. The missing
dates must come from somewhere, so you will need a table of dates that you
can outer join to.

Create a Cartesian product query based on this table of dates and your
Accounts table (i.e. it outputs every date for every account.)

In that query, use a subquery to sum
Deposits - Withdrawals
for the account and dates up to and including that date.

You now have the amount at close of business for every date, so you can
average it.

You will need some understanding of queries to implement that. (It is too
much to provide detailed steps in a newsgroup reply.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

My Overall objective, is to do this only for the purpose of calculating
average Balance over 30 days, without doing the headache of VB code, that
does calculations based on date spans. At this moment I am mapping out
that
code. My two options were from this info: 7/3/09 to 7/8/09 you had
$10,000
from 7/9/09 to 7/25/09 you had 7,000, do one of these:
1. Have this qry work so it puts up the balances for all 30 days, so I
can
do a quick add for a total balance for the month, and divide by 30 days.
2. Or doing it programatically and say, from the 3rd to the 8th is 5
days.
Multiply 5 times 10k, etc.

You could use an expression such as this:
DSum("[Deposits]-[Withdrawals]", "tblAccountActivity",
"([AccountID] = " & Nz([AccountID], 0) & ") AND ([ActivityDateTime] < " &
Format(Nz([ActivityDateTime], Now()), "\#mm\/dd\/yyy hh:nn:ss\#") & ")")

This assumes:
- a field named AccountID (type Number),
- a field named ActivityDateTime (type Date/Time),
- the combination of these 2 fields is unique.
The 3rd requirement is necessary: otherwise the concept of the 'previous'
activity on an account is undefined.

If you don't mind a read-only result, a subquery might be quicker. More
info:
http://allenbrowne.com/subquery-01.html#AnotherRecord

An easier solution is to do it in a report. You can add a text box to
your
report, and give it properties like this:
Control Source =[Deposits] - [Withdrawals]
Running Sum Over Group
Assuming you grouped on the AccountID, that should work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Ok, here is my situation. I have a table where you can enter deposits
and
withdrawals for an account involved in a specific investment. Then I
do a
query that returns a 30 day period report. I use Left Outer Join to
make
the
query, enter some data every day in the period (even if it is just 0s).
So,
from june 1 to june 30. There are 30 entries and where a withdrawal or
deposit is made that data is shown. My problem is I need a running
balance
total. So day one you invest 30 dollars. It should say $30 in the
balance
column the whole way down until you get to another activity. So a
withdrawal
of $10, would make it go to $20 for the rest of the way, etc. I use
NewBalance:
DSUM([Deposits]-[Withdrawals], "tblAccountActivity")

1. I dont know what to use in the second parameter, tblAccountAct,
holds
all
Activity across all Groups and investment plans.
2. What it comes up with, is it puts in the balance column the change
multiplied by the number of entries in that table. and then any time
there
is
no activity it goes back to saying 0.