Inventory Database Plus (1 Viewer)

[honey2wood]

Hi

I have been looking through the forum for a solution to an inventory database problem but can't find anything that helps.
I work with an inventory database that I produced a long time ago and it works well.
Basically the stock is booked in onto a transaction table where I have the ability to add stock deliveries together to give an overall stock.
Also using the same table, stock can be taken away as its used from the total stock giving me a total stock holding. That is all ok and works well.
My problem is that the stock is purchased in batches which I need to be able to identify where and when they are used.
I now have a form with Combo box that lets me select the item that I need which then populates a subform with all of the different batches received.
Now I need to be able to break these batches into 'Used' , 'Rejected' and 'Available to use'.
I think I need another Transaction table but not sure how to move the data from one table to another.
I have a bit more than basic knowledge of Access and know it can be done but just cant work it out.
I have read through this again and I think it makes sense to me

If you have read this then thank you.

 

[gemma-the-husky]

the problem with "batches" that you describe is that this seems to fly in the face of what inventory is all about. Inventory is supposed to be homogenous.

so if you have 1000 widgets, and buy another 500, you now have 1500 widgets. There is no way of identifying a specific widget as being from any particular batch - well at least not one available to an inventory system. I imagine for a system with traceability you are looking for something much more advanced, and a lot more expensive.

maybe the stock itself is labelled or marked with some identifying mark that could be used outside the inventory system.

you could store each batch in a separate location - but if a storeman swapped over an item from one location to another, how would you know, if there were no identifying marks?

 

[Uncle Gizmo]

I agree with Dave....

It would appear you need some sort of system to add a serial number to each item.

 

[honey2wood]

Hi Dave /
I think this is why most posts on inventory databases just give an overall figure. Traceability is the issue here. I am looking at parts for medical devices which need a degree of traceability.
Hi Uncle Gizmo
We do give an individual Goods Receipt number (GRN) when we receive each batch which is stored against each delivery.
I was looking at some way of using the 'Inventory Table' ID number to make each batch unique but was not sure how to use this in perhaps another table.

 

[jdraw]

It seems you need to record part along with the batch in order to get the traceability you need.

You receive 1 or more Batches
A Batch contains 1 or many Parts
You give out/dispense Parts from Inventory

I think you may need to record Part and BatchID for both receipt and for dispense.

If you need a finer level of granularity, then you will have to identify that and adjust accordingly. If you really need serial numbers, then readers will need some additional info.

 

[honey2wood]

Hi Jdraw,
Batches are normally in 100s
A lot of the time we have orders that will use all of a batch which I am able to deal with 1 order for 1 batch. But for about a third we have two or three orders per batch. I need to be able to know how many of a batch have been rejected or left in stock.

 

[jdraw]

How do/could you identify that a Part belongs to a specific Batch?
Do you affix a label????

 

[honey2wood]

each batch as it arrives is stored in a box with the GRN number attached to it. That GRN (Goods Receipt number) and batch no. is recorded on the work sheets to indicate what batch was used. This can then be traced back to the delivery.

 

[gemma-the-husky]

I think the batch number control needs to be outside the pure inventory control.

by all means have a field in your production routines to record the "actual batch reference used", but you aren't going to get an inventory system to "tell you" which batch to go and use. That's the difference, I think.

The production people will have to feedback the details they actually used. If you use bar codes it may be 100%. If they key in, there will be errors somewhere along the line.