Stock update - Calculated fields and lookups

[kerrydunk]

Hi there
We have our access database with a bit of a messy structure?
I am hoping someone can help iron out the issues.

We use our database to record sizes of our product. I am hoping to improve it by adding the items we have in stock to prevent manufacturing more.

Basically our main database called "Make & Model 1" has a list of various makes and model numbers, each model number lists various information needed to manufacture a replacement part.

Customers order the part and these part details are entered into a table called "Order Detail" What I would like to happen is that when the details are entered a calculated field adds the data entered to a text string. I need to match the text string to the same text string in a table called "stock" as there could be one part that matches hundreds of models.

Basically the "profile" "Colour" "height" and "width" make the string and this is what I need to match and tick a box / populate the number of items in stock. Eventually I would like this to reduce by the amount ordered but lets do one step at a time.

Sorry if I have confused anyone but am happy to explain anything I have missed.
I am using Access 2010
Thanks for reading
Kerry

 

[Pat Hartman]

Each SKU should have a unique identifier. You will have a product table that provides all the descriptive info. Then a child table with specifics such as color, size, etc. that define an SKU. It is this table key that is used in the OrderDetails table to connect the two. If you have only a single warehouse, you could use the SKU table to track inventory levels and reorder quantities.

There are many ways to manage inventory. The only auditable way is to use a transaction table that records all receipts and disbursements as well as adjustments caused by physical inventory corrections.

If you go with the "update the bucket" method, be aware that you are in for some issues. There always seems to be more than one place from which the inventory needs to be updated and all these places need to work in sync and if you miss one, your inventory will always be off so thorough testing is a must.

The simplest way to make this work is to join to the SKU table in the subforms that enter order items and in the subforms that enter receipts. Your code in the form can update the quantity field and when the order or receipt is saved, so is the inventory adjustment. But - a word of warning. In a busy environment with multiple order takers and some frequently ordered items, you will run into conflicts. Your users need to understand exactly how to recover from a conflict and you will need code to help them. So before you go with what on the surface seems to be the simplest method, figure out how to resolve conflicts when two order takers attempt to update the same inventory item at the same time.