AnsweredAssumed Answered

quantities due not updating properly

Question asked by user26728 on Mar 25, 2016



I have a supply delivery database I am working on.

A building requests various quantities of various items.

A delivery slip is generated with the list of items and quantities of each item delivered.

Sometimes the quantity of an item delivered is less than what was requested.


I have a Work Orders table, a Deliveries table and a Transactions table.

Transactions stores requests and deliveries.

A work order can have many requests and many deliveries can be made to a work order.

I want to be able to show a quantity due for each item (request) in the work orders.



Work Order::wid = Deliveries::wid

Transactions::wid = Work Order::wid



(there are other self joins to get portals to work)


My problem is, I can't figure out how to structure things so that the quantity due updates properly when a delivery is made.

Can anyone point me in a direction to make this work? I am sure there is some basic concept here that I am totally not seeing