Inventory Location Management

[dclark1991]

Hey Guys,

I am in the process of trying to set up a rather simple inventory management system for a manufacturing company. More specifically, its sole purpose is to track what goods are being imported and exported from the warehouse. I have set up 2 forms (1 for importing & 1 for exporting parts), each of which has fields for part number, location, employee, date, and quantity imported/exported.

I have been successful in setting these forms up so that clicking on the Submit button will add/subtract from overall quantities of parts. However, I have numerous locations (10A-F ---> 19A-F) that can hold 1 part each. I would like every import/export transaction to specify where the part is/was located, and to change its status accordingly.

Does this make sense? For example, Let's say I am importing X amount of Part A into inventory. I would open the IMPORT Form, enter the part number, my name, the date, the quantity, and the location I would like to place it in (for example 10A). Clicking on Submit would then not only increase the inventory of the part in my database, but it would also change the status of location 10A to "occupied". My ultimate goal is for me to be able to pull up a database/form/report, and see which location slots are occupied and which are vacant.

Is this at all possible? And if so, is there anyone that could maybe point me in the right direction? Like I said, I am a newbie to this, but I am eager to learn!

Thanks in Advance.

[rpeare]

Could you not just add a field to both your receipts and disbursements forms (and the related table or tables) to indicate a bin/location? Then all you'd need is a supporting table with valid 'locations' and you could summarize your data by location as well.

[orange]

Here is a link to an old post. The dialog may be useful and there is an attached A2003 database.
Good luck with your project.

There was also a related post, where I sent the OP to the link above. You may get some background info/comments from the thread dialog.

[dclark1991]

Thanks for your rapid responses. I understand what you guys are talking about, and I am pretty sure I already had this set up. However like I said, every location/bin can only hold ONE part. I would like to be able to pull up a table/form/form that will show me exactly which bins are vacant and which are occupied. Any idea of how I could go about this best?

[ItsMe]

I would not worry about the status of 10A. Instead, worry about the status of an inventory item. You can track the status of an inventory item via dates for Order, Receive, Dock, Ship, etc.

When an item hits the Dock, assign it a location. You can use a table that has locations (bins). For instance, the value of the Key field for 10A might be 20. You can assign the value of the Key field to a foreign key field in your inventory table.

You can determine which locations are available by using a query against inventory items that have a status of being in the warehouse.

[orange]

?? I'm not understanding??

You import X amount of a Part and you want to store it in 10A, but then you later say
"...every location/bin can only hold ONE part..."

Please clarify.

In overview:
It seems you have StorageBins, Parts and PartQuantity. And you import/receive Parts and export/retrieve/ship Parts. The task is to know how many of which Parts are where.
If this is correct then you have Receipt and Ship transactions. Receipt transactions add to inventory. Ship transactions subtract from Inventory. At start, and from time to time, you do a StockTake exercise to verify current Inventory (bin status,bin content (part, quantity)).

StorageBins have attributes name, location(coordinates), IsOccupiedYN
Parts have attributes name, description, weight, color, material, (serial number, model..??.)
PartQuantity would seem to be limited to integers. You may also have a UnitOfMeasure (doz, gross,each..??)

Transaction would have attributes type, date,Initiator, Part,Quantity,toLocation{bin number, customer}, fromLocation{customer, bin number}

Current Inventory (by Part) = Latest StockTakeQuantity(of Part)           
+Sum(ReceiptTransactionQuantity) 
- Sum(ShipTransactionQuantity)

[dclark1991]

Ah I'm sorry for the misunderstanding Orange. Let's say the company produces 10 different types of parts. These parts come on palettes, hence there could be X amount of part 1 on a palette. Each bin can only hold ONE palette. I already have most of what you described implemented, however I think the "IsOccupiedYN" is what I am looking for! How would I go about setting up my transaction forms so that when I enter a toLocation or fromLocation, the IsOccupiedYN would change accordingly?

Thanks so much for this!

[orange]

When you have a fromTransaction with Quantity 20 for Part K, you have to:
-check that there are 20 or more Part K in inventory
-if there are > than 20, you would fill the transaction, then leave or reset IsOccupiedYN to Y
-if there are exactly 20, you would fill the transaction, then reset IsOccupiedYN to N, then perform your ReOrder process (whatever that is)
-if there are less than 20, you can not fill the transaction request, so you could
--- fill the request with the quantity you have(partial transaction), issue a Backorder for the ---remainder, or if you have a ReOrder level and reOrderQuantity,then invoke that process and signal that a partial fromTransaction and set IsOccupiedYN to N