Update Product table, Quantity On Hand Field...

[FrustratedAtWork]

Someone PLEASE HELP.. I have a very small and somewhat simple A2000 dB (it's for placing book orders). I have an orders table and and order line item table (one order can have many items (books) ordered. When the user enters the data for a new order a single record with order number is placed in the order table, then the actual book info is placed in the order line item table. The only thing the product table is currently doing is being used for lookup values on the forms. I need to somehow take the value for the quantity for an item (book) ordered and subtract the quantity ordered from the Product table/Quantity on Hand Field. I then need to have the field updated with the new quantity on hand. Additionally, if an order item is deleted I will then have to take the quantity and add it back to the Product Table. Please help...

 

[zambezibill]

There are a few ways to keep your Quantity On Hand up to date. Create an update query (or use an equivalent SQL statement embedded in VBA code) and run it on the desired event.

However you should really do away with the field altogether. The initial thought is (gasp) scary, but there isn't any need for it. Inventory is a calculated value (stock in minus stock out) and calculated values need not be stored as they can always be recreated. A select query can calculate orders and returns for a given period and tell you what the difference is on a real time basis.

Cheers, Bill

 

[demoman]

Did Bill answer your question OK? He is correct on both accounts. You will need some code (no too tough) to update your QOH.

While item two is correct, it is not always the choice to be made. Personally, I agree with the design he describes. In fact, I have argued it myself. Unfortunately, I have also been a party to maintaining a centralized QOH for practical reasons.

In a large transactional environment, it is more efficient to maintain a single field's value, rather than re-compute the value by reading every transaction.