Running Total Loop

[Jeff Garrison]

I have a table that has an accrual field and a running balance field. My
question is...how can I do an update query or vba that will process the
record, which will have the accrual field, a time taken field, and a
balance? I'm assuming a loop...

The rows are dynamic...so there is a record for week 1, week 2, week 3, etc.

Here's what I'd like the end result to be...

Week Accrual Time Taken Balance
1 3.08 0.0 3.08
2 3.08 0.0 6.16
3 3.08 0.0 9.24
4 3.08 8.0 4.32

I'd like to be able to run a query for any given week and see the time taken
and the balance.

If I'm going about this the wrong way, I'd appreciate that input as well.

Thanks in advance.

Jeff

 

[John W. Vinson]

I have a table that has an accrual field and a running balance field. My
question is...how can I do an update query or vba that will process the
record, which will have the accrual field, a time taken field, and a
balance? I'm assuming a loop...

The rows are dynamic...so there is a record for week 1, week 2, week 3, etc.

Here's what I'd like the end result to be...

Week Accrual Time Taken Balance
1 3.08 0.0 3.08
2 3.08 0.0 6.16
3 3.08 0.0 9.24
4 3.08 8.0 4.32

I'd like to be able to run a query for any given week and see the time taken
and the balance.

If I'm going about this the wrong way, I'd appreciate that input as well.

Thanks in advance.

Jeff

Storing calculated data - such as your running sum - in a Table is neither
necessary nor good design. The field should simply NOT EXIST in your table!
Instead, calculate it on demand in a Query:

SELECT [Week], [Accrual], [Time Taken], (SELECT Sum([Accrual]) FROM tablename
AS RunningSum WHERE RunningSum.[Week] <= [tablename].[Week]) AS Balance
FROM tablename;

If there is an account number and you want separate sums for each account, or
other criteria, this will need to be modified - but the sum calculation in the
subquery (and NOT in any table field!!) is the essential part.

 

[Dale Fye]

Or better yet, create a report.

Once you create the report, copy the Accrual textbox and set the RunningSum
property on the Data tab to OverAll or OverGroup.

--
HTH
Dale

Don''''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I have a table that has an accrual field and a running balance field. My
question is...how can I do an update query or vba that will process the
record, which will have the accrual field, a time taken field, and a
balance? I'm assuming a loop...

The rows are dynamic...so there is a record for week 1, week 2, week 3, etc.

Here's what I'd like the end result to be...

Week Accrual Time Taken Balance
1 3.08 0.0 3.08
2 3.08 0.0 6.16
3 3.08 0.0 9.24
4 3.08 8.0 4.32

I'd like to be able to run a query for any given week and see the time taken
and the balance.

If I'm going about this the wrong way, I'd appreciate that input as well.

Thanks in advance.

Jeff

Storing calculated data - such as your running sum - in a Table is neither
necessary nor good design. The field should simply NOT EXIST in your table!
Instead, calculate it on demand in a Query:

SELECT [Week], [Accrual], [Time Taken], (SELECT Sum([Accrual]) FROM tablename
AS RunningSum WHERE RunningSum.[Week] <= [tablename].[Week]) AS Balance
FROM tablename;

If there is an account number and you want separate sums for each account, or
other criteria, this will need to be modified - but the sum calculation in the
subquery (and NOT in any table field!!) is the essential part.

 

[Jeff Garrison]

John -

Got that to work on the report...thanks. How can that be adapted to be used
on a form, where the Balance will automatically get updated when the Accrual
or Time Take fields get changed? My assumption is to fire an After Update
event, but I'm not sure how to execut the Update via vba.

Thanks.

Jeff

I have a table that has an accrual field and a running balance field. My
question is...how can I do an update query or vba that will process the
record, which will have the accrual field, a time taken field, and a
balance? I'm assuming a loop...

The rows are dynamic...so there is a record for week 1, week 2, week 3,
etc.

Here's what I'd like the end result to be...

Week Accrual Time Taken Balance
1 3.08 0.0 3.08
2 3.08 0.0 6.16
3 3.08 0.0 9.24
4 3.08 8.0 4.32

I'd like to be able to run a query for any given week and see the time
taken
and the balance.

If I'm going about this the wrong way, I'd appreciate that input as well.

Thanks in advance.

Jeff

Storing calculated data - such as your running sum - in a Table is neither
necessary nor good design. The field should simply NOT EXIST in your
table!
Instead, calculate it on demand in a Query:

SELECT [Week], [Accrual], [Time Taken], (SELECT Sum([Accrual]) FROM
tablename
AS RunningSum WHERE RunningSum.[Week] <= [tablename].[Week]) AS Balance
FROM tablename;

If there is an account number and you want separate sums for each account,
or
other criteria, this will need to be modified - but the sum calculation in
the
subquery (and NOT in any table field!!) is the essential part.