Updating available QTY in Stock

[ratib hussaini]

I making a database for inventory control. The company has a warehouse and 2 stores, you can name them Location A, B and C. The same products could be in 3 of them. recently I made a customer order form where I can enter customer order, but I cant figure out, how to update the inventory. e.g if a product is sold from location A it should automatically update the inventory in location A.



And also I want a form for internal transaction. e.g. If company moves some of the items from location A to B or other,, after filling form it should automatically update all locations. Any idea from your side will be appreciated. thanks

[alansidman]

crossposting

http://www.access-programmers.co.uk/...d.php?t=236408

[June7]

Saving aggregate data is usually not advisable. The data can become out-of-sync with the source info. Audit trail is broken, error in saved value difficult to identify and fix.

Ideally, save raw data in relational database then calculate aggregate data, such as inventory balance.

Raw data would be transactions for product in/out/transfers.

Building this db will not be quick and easy. There are off-the-shelf apps already available for this and even they have issues - http://www.sleeter.com/blog/2012/09/...base-problems/

Here is an Access template for inventory tracking but I expect it is designed for single site:
http://office.microsoft.com/en-us/te...001018458.aspx
I don't know if it saves aggregate values or calculates balances on-the-fly but you might want to check it out.