Updating several records with "carry forward" function

[DKS]

Hi,

How can I write a piece of code that goes through some selected records from
my table and updates few fields.

Let us say that I have the following fields in a record
* transaction date
* starting balance
* transaction amount (can be positive or negative)
* new balance = starting amount + transaction amount

For the next chronological transaction date the starting balance is
identical to the new balance for the previous transaction date. Assume no
duplicates in transaction date.


With the above "architecture" of my table, I would like to implement some
logic whereby if I change the "transaction amount" for a given transaction
date, then automatically the new balance is correctly calculated for that
transaction. Thereafter the next transaction date record is automatically
updated for the starting balance. And because starting balance influences
the new balance this change is made. And since new balance is changed, the
next transaction date record has a new value for the starting balance. And
so on the ripple effect continues till we reach the end of the records-set.

How can I program this?

 

[Douglas J. Steele]

You shouldn't be storing the calculated value in your table, to prevent the
very problem you're trying to overcome.

Instead, your calculation should be done in a query, and you'd then use the
query wherever you would otherwise be using the table.

 

[DKS]

Yes, I know. But the architecture is inherited and so cannot do much to
change it (in a short time frame).

Thus the slightly complex workaround (if there is one).

thanks in anticipation.

 

[Alex Dybenko]

Hi,
as I see - you can just run an update query for records, where date > date,
when adjustment occurs, and add/subtract difference from starting/new
balance

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Rod Plastow]

Don't think that will solve DKS's problem - he wants to carry forward the
closing balance as the opening balance for the succeeding record. The only
way I can think of is to step through the records one by one. Urgh! DKS:
Follow Douglas' advice and implement a better design asap.

Rod

 

[DKS]

Rod (and others),

Even if I change the architecture as suggested by you/Douglas, I would still
need an almost similar "programming" code to show me for each transaction the
following:

date of transaction (from table)
starting balance (computed and coming from calculations based on logical
previous record)
transaction amount (from table)
new closing balance (computed but fortunately from the displayed record and
so easy)

Thus look at it whichever you want I have the same problem to solve.

Do keep in mind that I am not looking for a QUERY to accomplish this. I am
looking at programming code. And I believe that some basic code can be
written that via a DO ... or WHILE/WEND combination runs through the
different records and record-by-record applies the changes. Is it that
complex to achieve via access?

Thanks.

 

[Rod Plastow]

No, it's not complex - it's just that we have concerns over the inefficiency
of your design. Let me give you some sample ADO code that does what you want
for the whole table. Later you can intoduce a WHERE clause to start at the
affected record and propagate the change forward. Even later you can enclose
the code in a BeginTrans/CommitTrans pair so that you update all or nothing.

Dim rstMyTable as ADODB.Recordset
Dim binFirstTime as Boolean
Dim curBalBf as Currency

binFirstTime = True
With rstMyTable
.ActiveConnection = CurrentProject.Connection
.LockType = adLockOptimistic
.Open "SELECT * FROM MyTable ORDER BY [transaction date]"
Do Until .EOF
If Not binFirstTime then
![starting balance] = curBalBf
Else
binFirstTime = false
End If
curBalBf = ![starting balance] + ![transaction amount]
![new balance] = curBalBf
.Update
.MoveNext
Loop
.Close
End With

You have to substitute your own names in the above. If you want the DAO
code then give me some time as I abandoned DAO some years ago thinking it was
dead. Wrong!

Rod

 

[Rod Plastow]

Ahhh

I've done it again; I haven't instantiated the recordset; that's what comes
of doing things off-the-cuff.

Although not the better way, include the New keyword in the declaration
statement for the recordset. It should now read:

Dim rstMyTable as New ADODB.Recordset

Sorry,

Rod

 

[DKS]

Thanks Rod.

If I had to change the architecture and I had to display for each
transaction the starting balance and the ending balance, would it not be
equally complex? Or am I missing some simpler means of implementing an
alternative and better architecture?

Thanks.

 

[Rod Plastow]

The guidelines of data normalisation and relational database design are
quickly and easily learned - well up to third normal form. However if fully
applied in all cases the data model and database design become monsters with
a proliferation of entities/tables across which the base data is fragmented
and the derived data omitted. Much effort is then needed to reassemble this
fragmented data into useable data views. So the 'art' (as opposed to the
'science') of all this is to know what liberties to take with those
guidelines in order to produce a viable, efficient and manageable design.
The discipline of fully normalising before relaxing some of the rules leads
to an understanding of what limitations are built into the design.

Let's take a trivial but oft encountered normalisation example: an address.
Strictly speaking all buildings in a road have a relationship with that road
and the rules of normalisation say you should create one entity for the road
and one entity for the buildings that is a child of the road entity. Unless
you are a mailing organisation or a street directory service you would never
consider doing this; instead you allocate a large enough text attribute to
hold all the data concatenated as a string. The limitation this puts on your
design is that you can never (easily) find all the buildings in the same
road. But is this important? Probably not.

In your particular case the discussion has focused on derived values. The
guideline says don't store them but recalcualte them every time they are
needed; but try criticising the designer of an accounting application by
pointing out that the monthly closing balance for each account is a derived
value and should not be stored! (I'm sure there are accounting applications
out there that do not store monthly balances but this is not the norm.) The
decision to include derived values is based on many factors: gut-feel,
experience, frequency of retrieval, complexity of calculation and, sometimes,
post-implementation performance. (Oh I forgot history/archive
considerations. This will be the main argument used by that accounting
application designer.) Data wharehousing applications deliberately store
derived values for ease of data retrieval/mining/whatever.

However both Alex and I immediately baulked at learning of a design where
each transaction has a starting (balance b/f) as well as the transaction
value. Typically there will be thousands/ten of thousands of transactions.
Not only does the design double the data store space but there is a
performance hit every time a transaction is added or changed as the effect is
propagated forward. There's not a closing balance as well, is there?

(Hm, opening balance, transaction value, closing balance; did this
application migrate from Excel?)

I have one accounting application where there is a procedure named,
BalanceAsAt whereby I can calculate the balance at the start of a particular
day for any account. It's called as and when needed.

If you need the starting balances transaction by transaction on a report you
can calculate them as you format each line, given you know a starting
balance. Doing the same for continuous forms is more tricky and a lot
depends upon whether the user is allowed to update the transaction value in
such circumstances.

This have been a longish post touching on some popular debate topics. The
bottom line however is that you, and only you know your data and requirements
well enough to make the final decision. If as I hinted this application has
its origins in Excel and the user is just used to seeing a starting balance
then try to ween him off it.

Rod