Question about building an inventory manager

I’m pretty new to Access but want to build a relatively simple inventory manager but I’m kind of stuck at the concept-stage.

I work at a small company that receives big batches of product (productA/ProductID) in different container sizes, and spread them out over the company. Those buckets go back in storage and are used up over longer periods of time, so we regularly notice a product is gone when it’s too late.

My strategy was to make a form that tracks changes to batch codes, and use a look-up file for the inventory department so they can check if they have the right product on the form like this:

Select:BatchCode

LookUp: productName

LookUp: ProductID.

LookUp:CurrentAmount

Add/Substract: #enterAmount

and let this make an entry in an event-table.

But apparently these buckets have non-unique BatchCode (e.g ProductA and productC could have the same batchcode), sometimes spread over multiple buckets and we have multiple batch codes in stock. like this:

ProductID ProductName BatchCode Amount Date
123456 ProductA 1AB2 500g 1-1-20
123456 ProductA 1AB2 250g 1-1-20
444955 ProductB 4BC3 200g 4-2-20
123456 ProductA 2PK4 400g 5-3-20
305825 ProductC 1AB2 200g 6-6-20

so my whole strategy of just using a look-up won’t work. My newb brain’s next logical step would be to make separate tables for each product, but that would result in roughly 50 very small tables and I’m pretty sure that would be database nightmare. Anyone can help me try to make sense of this?

submitted by /u/KipPilav
[link] [comments]