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?

 

[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.