Running Total in Queries

[dje]

I have searched the discussion group but havent found a way to make my query
calculate a running total.

I have a query [MTM App Buyer] which is made up of:

Field MyDate Tot Bal USD
Table Daily Price Non Cash Trans
Total Group By Sum
Sort Ascending

The first selection shows dates and the second selection shows daily
movement of values as I buy and sell an asset. I want a third column which
shows running balance of those values by date i.e.

1/1/08 100 100
2/1/08 25 125
3/1/08 -50 75 etc

I know I can do this in a report but I want to use the running balance in a
union query which aggregates 6 or 7 similar running balances to come up with
an overall running balance.

I have become aware of the Dsum expression but cant seem to get the syntax
to work. Can anyone help me overcome this?

Thanks

 

[Gary Walter]

Hi dje,

If I did not use a report summing,
I honestly would probably do this
running sum in a scratch table
(say "tblRunSum")

You have not given enough table details
to be specific, but, in general, temporarily
turn your query into a maketable and run.

Add a couple of fields to table
ID autonumber pk
Asset Text(255)
RunSum Long (Default 0)
OverAllRunSum Long (Default 0)

I would start the process by clearing tblRunSum
in code.

CurrentDb.Execute "DELETE * FROM tblRunSum",dbFailOnError

Then, run append queries to send date/asset/sum
to table for your 6 or 7 aggregates.

Then, run update query to compute RunSum
over Asset and/or compute OverAllRunSum
over all Assets using date (and maybe ID).

UPDATE tblRunSum AS T
SET T.RunSum = DSUM("TotBalUSD",
"tblRunSum","[Asset]='NonCashTrans'
AND [MyDate]<=#" & T.MyDate & "#")

in code, replace " with ', and ' with 2 single quotes

strSQL="UPDATE tblRunSum AS T " _
& "SET T.RunSum = DSUM('TotBalUSD', " _
& "'tblRunSum','[Asset]=''NonCashTrans'' " _
& "AND [MyDate]<=#' & T.MyDate & '#')"

CurrentDb.Execute strSQL, dbFailOnError

just a general method based on general info....

good luck,

gary

"dje"wrote:

 

[dje]

Thanks for your assistance. It shows why as a novice I didnt have a chance of
getting to the right answer! It takes a little digesting and I will be
experimenting over the next few days.

Hi dje,

If I did not use a report summing,
I honestly would probably do this
running sum in a scratch table
(say "tblRunSum")

You have not given enough table details
to be specific, but, in general, temporarily
turn your query into a maketable and run.

Add a couple of fields to table
ID autonumber pk
Asset Text(255)
RunSum Long (Default 0)
OverAllRunSum Long (Default 0)

I would start the process by clearing tblRunSum
in code.

CurrentDb.Execute "DELETE * FROM tblRunSum",dbFailOnError

Then, run append queries to send date/asset/sum
to table for your 6 or 7 aggregates.

Then, run update query to compute RunSum
over Asset and/or compute OverAllRunSum
over all Assets using date (and maybe ID).

UPDATE tblRunSum AS T
SET T.RunSum = DSUM("TotBalUSD",
"tblRunSum","[Asset]='NonCashTrans'
AND [MyDate]<=#" & T.MyDate & "#")

in code, replace " with ', and ' with 2 single quotes

strSQL="UPDATE tblRunSum AS T " _
& "SET T.RunSum = DSUM('TotBalUSD', " _
& "'tblRunSum','[Asset]=''NonCashTrans'' " _
& "AND [MyDate]<=#' & T.MyDate & '#')"

CurrentDb.Execute strSQL, dbFailOnError

just a general method based on general info....

good luck,

gary

"dje"wrote:

I have searched the discussion group but havent found a way to make my
query
calculate a running total.

I have a query [MTM App Buyer] which is made up of:

Field MyDate Tot Bal USD
Table Daily Price Non Cash Trans
Total Group By Sum
Sort Ascending

The first selection shows dates and the second selection shows daily
movement of values as I buy and sell an asset. I want a third column which
shows running balance of those values by date i.e.

1/1/08 100 100
2/1/08 25 125
3/1/08 -50 75 etc

I know I can do this in a report but I want to use the running balance in
a
union query which aggregates 6 or 7 similar running balances to come up
with
an overall running balance.

I have become aware of the Dsum expression but cant seem to get the syntax
to work. Can anyone help me overcome this?

Thanks