Warehouse Inventory Database

[trickg]

Has anyone here ever developed a project to maintain and control inventory for a warehouse?

I'm trying to get my mind around this new project, and I'm not quite sure how I want to handle it. (i.e. tables, relationships, etc) I figured that someone on here is likely to have done an inventory control program before and might be able to shed some light on it.

Basicall, I need to know how many of each item I have in inventory, where in the warehouse it is located, quarantine items coming in, and quarantine items going out.

Initially this is going to be done in Access, and then it will be set up as a web application so that it can be accessed not only from the warehouse, but also from a head office.

Any tips or help (or even fully completed projects! ) would be greatly appreciated.

 

[Full_Williams]

Trick,

The database wizard has an Inventory type you might want to check out.

When you open access from Start-->Programs click Access database wizards then choose Inventory.

Full Williams

 

[trickg]

Yeah, I've done that, but unfortunately, it's not really what I need.

Basically, I'm trying to track pieces of office furniture and parts to make up cubes.

Here is what I have so far:

ITEMS
Item_ID
E_Number (This number is unique, but I want to use “Item_ID” as the PK)
Item_Desc
Category (For Cat and Sub cat, I’m going to store the text from the lookup tables rather than the ID)
Sub_Category
Image_ID (can have multiple items with same image)
Size
Color

CATEGORY
Cat_ID
Cat_Desc

SUB_CATEGORY
Sub_Cat_ID
Cat_ID
Sub_Cat_Desc

IMAGE
Image_ID
Image_Desc (not sure if this is needed)
Image (Currently an OLE data type – when this goes to the web, this may get changed to a unique file name)

LOCATION
Location_ID
Location_Desc
Location_Number (Probably going to be designated with an alphanumeric locator number)

ITEM_LOCATION
Location_ID
Item_ID
QTY

I think that I can get this basic structure to work for just the items, the locations and the quantities, but I’m stumped as to how I’m going to handle new items, and items to be shipped, as well as maintaining a history transaction log as to what, how many and when certain items come into or go out of the warehouse.

Ideas?

 

[Pat Hartman]

For Cat and Sub cat, I’m going to store the text from the lookup tables rather than the ID

Very bad idea! You need to save the ID's. That way you can enforce RI.

Location probably needs to be broken down at least into warehouse and bin so you can support multiple warehouses and multiple areas in the same warehouse.

The last table is the tricky part. You are showing a current balance. You really need a transaction table that tracks the items as they are ordered, received, moved, sold. You also need to handle physical inventory adjustment transactions so you can change the current balance if a physical count differs from what the system is reporting. If your transaction volume is low enough, I would suggest not keeping a current balance at all. Just calculate it as needed. Take a look at these threads:
http://www.access-programmers.co.uk/forums/showthread.php?t=4117&highlight=Inventory
http://www.access-programmers.co.uk/forums/showthread.php?t=68407&highlight=Inventory+Cycle+Count

 

[trickg]

Pat Hartman? You aren't from Nebraska by chance are you?

Here is my thought on saving the text rather than the ID. For historical purposes, you may not want to save the ID because that forces you to keep every category that has a corresponding item, even if that category becomes obsolete and is no longer needed - hence, deletable. Also, I have had some problems with databases in the past where due to the database being overly normalized, it made it a real pain in the butt to create some of the reports that were needed.

Keep in mind, I'm still int the white board phase of this project and I'm open minded to be flexible enough to change my schema and how I'm going to handly my data. Typically in my databases, my base table regarding the entity that the database revolves around usually has very little text and is mainly made up of IDs from related tables.

As for your last paragraph, if I had an answer to how I was going to do that, I wouldn't have posted here on the forum. Please don't take that the wrong way - I understand the need to track transactions and the entry and exit of items in this warehouse, but I haven't come up with a schema or methodology with which to do that.

The unfortunate truth is (as usual!) this project is quite a bit more complex than my boss thinks it is. He usually thinks that this kind of thing can be thrown together in a matter of days with very little forethought and planning, and we go round and round that sort of thing.

Keep the ideas coming. I'm not decided on whether or not to store IDs or text for those two things. I would like to maintain the ability to delete a category if it is no longer needed, yet be able to pull up an item that is still in the warehouse from that category that has that Cat description.

 

[kdm3]

The easiest way of storing incoming stock, outgoing stock and stock adjustments (i.e. in response to a Stock Take), is to have a transactions table (as already outlined) and then have a field specifying the TYPE of transaction - whether this be an order receipt, adjustment or whatever - which will enable you to keep track of all movements. E.g. If there are excessive Adjustments you know there's a problem.

 

[Pat Hartman]

Here is my thought on saving the text rather than the ID. For historical purposes, you may not want to save the ID because that forces you to keep every category that has a corresponding item, even if that category becomes obsolete and is no longer needed - hence, deletable. Also, I have had some problems with databases in the past where due to the database being overly normalized, it made it a real pain in the butt to create some of the reports that were needed.

- Sorry, not a good enough reason. RI is simply too important. If you want to be able to prevent future assignments, add an ActiveFlg. Set the default to Yes so that new records get added as active. When you no longer want to be able to assign a code, set the ActiveFlg to No. Then in your BeforeUpdate event for the combo where the code is selected, check the ActiveFlg and prevent the assignment of any code that is no longer active.

I am not sure that anyone can tell you exactily how you need to keep inventory except that you really need to use transactions in order to have any auditiblity. Review the posts that I referenced and search for more. There has been a lot of advice posted on this topic. Once you get more specifics, we can help you better.