Chemical item stock management

[anidiot]

Hello,



I need some help figuring out how to create a simple inventory management please. What I will be doing is create a table with all chemicals in stock. I also need to create another table with formulations (lets say each formulation contains 3 ingredients). So I would need to enter each formula with their respective ingredients and quantities.

For example, let's say Formula XB12 contains 5kg of A, 4kg of B, and 1kg of C chemicals.

When I make a 20kg mix XB12, it should reduce the stock of all 3 ingredients (10kg of A/ 8kg of B/ 2kg of C) from the current stock.

What would be best way to achieve this? Are there any examples that anybody knows that I can play with?

Many thanks!

[June7]

"Simple" is relative.

Review http://allenbrowne.com/AppInventory.html

[CJ_London]

I would add a variation to Allen’s example which can be applied in many situations.

The principle Allen uses is correct but rather than having three tables to record transactions (in/out/stocktake), use one with perhaps an additional field to provide more information such as transaction type which might include all the other things that can happen - recorded wastage/spoil for example.

Quantity can then either be plus or minus depending on whether received or used (simpler calculations but a bit more work to convert a plus to a minus when the record is saved) or all positive and a bit more work to do the conversion at calculation time, but easier for handling record saving - users won’t want to have to remember to include a minus sign. In terms of user interaction this is closer to Allen’s solution

[anidiot]

Thanks for the comments.

I get the logic, but in my case, it really is simple. I really would appreciate it if you could give me some direction - I've designed a fairly basic table and equally simple form where the stock can be entered/updated (weekly). What I want to is, most importantly, reduce the stock based on formulation. I've given a basic example on my original question.

If you can give me some direction that would be great.

Thanks

[CJ_London]

We’ve provided direction as to how to do it properly. For myself really not interested in spending a lot of time trying to do it the wrong way. Just too many problems

[Welshgasman]

Crossposted https://www.access-programmers.co.uk.../#post-1769217

[June7]

Write code that updates table. This could be as simple as code behind form while focus is on record to edit Me!fieldname = Me.textboxname or Me!fieldname = some expression or function to calculate new value. The real trick is figuring out what event to put code into.

I wouldn't go this route either.