Raw materials

Question

Hi,

I've just downloaded the "Northwind sales web database" and it's almost perfect for what I want ... except 1 (major) problem

It is currently set up with

Customers who make orders with multiple products - which is linked to the Stock inventory

However the products I sell are made up of raw materials, e.g. I sell a cushion, which is made from 1/2 metre of fabric and a zip. Is there any easy way to add another layer (raw materials) within the products? So there are

Customerswho make orderswith multiple productsmade from multiple materials- which is linked to the Stock inventory

So if I sell 10 cushionsit currently adjusts stock levels of the "cushion" product to minus 10, but I want it to adjusts the the stock levels of "Fabric" minus 5 metres and Zips to minus 10?

Then when I buy new stock, it will be added to the zips and fabric not the cushion.

... or maybe there is another part / different database I should download?

Many thanks, Sarah

p.s. I'm new to access - so hope that makes sense!

Answer 1

What you are describing is called a Bill of Materials (BOM). This is not an easy thing to deal with. One question is whether your BOM can have multiple levels. So that a component of a product may, itself, be made up of different raw materials. 

Also, what about waste? Your cushion uses 1/2 metre of fabric to make, but how much is left on the cutting room floor?

I would suggest doing research on Bill of Materials design to see how to setup such an app. You may also find templates to help.

Answer 2

Sure. At a high level ... off the top of my head

You need a couple of new tables. 

Materials:

material_ID   unique number (mandatory)

description   text(?)  (mandatory)  (the descriptive name the material is known by, ie cotton, steel, 2x4 Spruce G1S etc)

Color(?)  text (optional)  (is color important, I guess it is)

quantity_in_stock   number (mandatory)

quantity_in_stock_units  text (mandatory)

Product_defined:

product_ID unique number (mandatory)

description (descriptive text product is known by, ie Pillow)

Build_list:

Product_id

material_id

quantity_used