Update field with calculation based on fields from several records

[AndrewRossington]

Hello all

I hope the title wasn't too confusing. I wanted to summarise exactly what it is I need, but I find it difficult to put into words.

If I may explain:

I have 3 tables - Products, Orders and Deliveries.

They're set up in a one-to many relationship so that One Product has Many Orders, and each One Order has Many Deliveries (With me so far?)

On the sub-form based on the Deliveries table, I have 2 fields relevant to my question - QuantityReceived and QuantityRemaining.

These fields hold exactly what you'd imagine. What they don't do is automatically update the QuantityRemaining field based on the figure in the QuantityReceived field. (That is, every time you create a new record for detailing another delivery, the current QuantityReceived value should be subtracted from the previous QuantityRemaining value and the result placed in the current QuantityRemaining field.

Is this possible? And if so, how do I go about implementing this? (The database has been in use for a while and, as such, the fields already contain data. Is it possible to implement this for any new records created or would it have to be from scratch?)

For your info:

I use Access 2000
I've searched online but there doesn't appear to be any tutorials for this kind of thing.
I have what I consider very basic VBA know-how.

If anyone has any ideas, I'd appreciate it.

Thanks very much for your time.

 

[AndrewRossington]

Thank you for the help with this. Unfortunately this project has been shifted down my priority list at work, so I won't be able to try this any time soon.

I will let you know how this goes in the future.

Thank you again.