Need help on inventory database design

[mhudson]

I am struggling with how to design the tables for an inventory database for our company. What is giving me problems is that we have 3 levels of inventory; WIP, Packed Goods, and Shipped Goods. Of course, what gets packed comes out of WIP, and what gets shipped comes out of Packed Goods. We are currently tracking about 50 different part numbers. Anyone have any ideas? Thanks in advance.

 

[genomon]

A good example is the Inventory Database that ships with Access. Click on "New From Template" then General Templates|Databases|Inventory Control.

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[scorpio1769]

Also I suggest that you have a table with a date/time field for each status you may have: WIP, Shipped, etc. Over the life of the part you need to track when did it change from wip and then ship, etc.

ID
PartNumber
WIPDate
PackedDate
ShippedDate

Reply if you need more help./

 

[ajetrumpet]

I assume you get daily reports about the status of your parts. If you do, you could easily set up some kind of automation system in Access that transfers data from FORM to FORM via the criteria you set. That way the updates that you make manually can be limited. I think you can set such a system without knowing any VB code, but I'm not 100% on that.

You could also try what I did....
***I set up a transaction spreadsheet in Excel (easy for data entry using Excel FORMS), then I linked that file to a DB in Access. I used the table and Access functionality to automate everything (all changes) made to the spreadsheet.

In short, used Excel for data and used Access for quick reference and searchability.
Don't forget, there are more programs than Access that can do the job! =)

 

[mhudson]

As a matter of fact, I am currently using Excel to perform this tracking, but it sure seems tedious to do it that way. Each day I have to insert new columns to enter the WIP, Packed Goods, and Shipped Goods quantities for the previous day. I then have columns for calculating what remains in each category for all the part numbers with transactions. Of course, what gets packed is subtracted from WIP, and what gets shipped is subtracted from Packed Goods. The end result is what quantities are available for every part number.

I will try to do as you have suggested and see what I can make work. Don't be surprised to hear back from me soon! Thanks for your help and quick reply.

 

[ajetrumpet]

are the suggestions listed here working for you?
I am also curious about something else...do you need to archive everyday's numbers (WIP, PACK, SHIP) for EVERY part number over the life or demand cycle of the part? If you need to store this much data, I wouldn't be using Excel at all. Access functions are much easier to work with, I'd import all the info. into a DB now and start setting up your queries.