Manufacturing Inventory Database

[mobster]

I need to create a small inventory database tracking product in stock and "x" amount of raw material to create it.

Is there a way, using MS Access that I can track inventory of the product as well as update how much material was used to create the product?


I am new to access but I know how to create a database and enter the fields I want, eg: product id, model number, product color, in stock, re order, lead time.

My problem is associating another table to deduct/or add the amount of material based on totals.

In a nutshell, I want to track product inventory as well as track raw material in stock to create more.

Thank you for any help!

 

[Steve R.]

Two tables may do the trick, but I think that there is a third table that should be considered.

**After reflection. Four tables appear to be needed.

Table #1 ORDER table that would show the product moving out-the-door.
Table #2 INVENTORY table showing what you have on hand. The difference between orders and inventory would "trigger" production to keep a minimum inventory level.
Table #3 RAW MATERIAL table would contain the name (unique_id) and quantity of the "raw materials" to make an item from the inventory table. The INVENTORY table would use RAW MATERIAL table to determine how much raw material product to order.
Table #4 Would contain the product information for Table #3. Cost, amount on hand, supplier, etc.

 

[mobster]

Thank you very much for your reply.

firstly, I don't really need the cost. This will be for inventory amounts only. We handle invoicing, po's and shipping in another program.

I need this for tracking and projection only.

The main question that concerns me is linking the tables to perform adding and subtracting of material based on quantity. Is there a formula for this?

Or, would I just need to assign a relationship between the product inventory and raw material inventory. If so, How do I go about this. Let's say for instance, I deduct 100 parts and the 100 parts use 50 raw material to make the part, how do I deduct from one table an amount edited by another one?

Am I making sense?

Thank You again

 

[Steve R.]

The RAW MATERIAL table provides the "formula". The tables would be linked by a productidnumber.

If you have an order for four boxes (ORDER TABLE) and only two are in inventory, then you would need to produce two boxes. The INVENTORY table would multiply the production requirements contained by the RAW MATERIAL table by 2. (ORDER-QUANTITYonHAND)*(RAW1 +RAW2+RAW3)

In terms of tracking, you would mimic a spreadsheet. One field for starting inventory, one field for usage (can be tracked by order#), and a computed field for the balance. This can be accompished through a query using all the tables and creating a form based on that query.