Process update query in specific order

[Guest]

I am attempting to post shipments against Purchase orders. I have a one to
many relationship from the purchase order file to the shipment file.
(Multiple Shipments)
I have a shipment number field in the shipment file. I need to process the
shipment file in shipment number sequence to record the remaining balance.
The key index field is the shipment number. When I process the update query,
it does not process in the key field order. How can I get the update query
to process in order by the key field?

 

[Tom Ellison]

Dear Byron:

There are some conceptual problems with what you propose to do here.

The idea of controlling the order in which an update proceeds is
fundamentally outside the scope of what a "Fourth Generation Languate"
(4GL) is meant to do. But then, recording a "remaining balance"
within a table is equally foreign to what databases are designed to
do.

Starting with the "remaining balance" problem, the proper way to
accomplish this is to realize that this is a "derived value" that
should not be stored at all. It should be generated dynamically
whenever it is needed. Otherwise, every time a "transaction" that
calculated into the balance is changed, added, or deleted, a whole
series of subsequent "remaning balances" through the set would have to
be changed.

The one solution to this, on which foundation much of database
operations are based, is to not record the "remaining balances" in the
table at all, but derive it from the data at the moment it is needed.
If you'll just agree to that (and we can help you obtain that running
sum) then these problems will simply vanish.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

Thanks Tom,
I will calculate the balance on the report