How to update subsequent records in a table

[irish634]

Hello,

I am trying to create a checkbook type database. I am stuck where if I need to edit a record, I need to update the running balance for the subsequent records but not the previous records.

For example: I have 10 records in a table. If I update a "withdrawal" value in Record 5 which in turn updates the "Balance" for record 5, how do I update the balances in records 6-10 without updating 1-4?

Thanks

 

[genomon]

You could loop thru a recordset beginning with the record after the edit, using UPDATE...SET SQL.

"Time flies like an arrow; fruit flies like a banana."

 

[lespaul]

you shouldn't be keeping the running balance in a table, you should calculate it on the fly in a query. This is why you don't store calculate values, breaks the rules of database design.


Leslie

Have you met Hardy Heron?

 

[genomon]

I swore I would never admit this, but Excel does this sort of thing quite nicely.



"Time flies like an arrow; fruit flies like a banana."

 

[Golom]

... but Excel does this sort of thing quite nicely.

Yes it does ... and maybe there's a lesson there.

Access may be a hammer when what you really need is a screwdriver. You can make it work but it isn't the right tool for the job.

 

[irish634]

Hi. I know excel does this quite well.
I want to use a "hammer" as you put it because I want to be able to report more efficiently.

Thanks for the input.

 

[lespaul]

and that's fine, you can create a checkbook application in a database, but you shouldn't store the "balance".



Leslie

Have you met Hardy Heron?

 

[SeeThru]

Yup

All you need is

Starting Balance
Change (positive or negative)

This cna be run in report to give a current balance per line, or a balance for the report as a whole (or both)

It makes for much easier updating, especially if you need to go back and made a correction.

You can even get a starting balance by getting the user to 'reconcile' the bank account at irregular periods, but this could make for some interesting reporting if the reconcilliation was half way thruogh a reporting period.

SeeThru
Synergy Connections Ltd - Telemarketing Services