Finished product is also a raw material (1 Viewer)

[Watergirl]

I'm new to Access (2002) and have been trying to learn by reading books, posts, forums and practicing with models. I've set up a database for inventory, primarily to track the cost of goods sold as raw material costs change from month to month. So far I have tables for raw materials, container sizes, suppliers, and finished products. But I have a problem - in some cases a finished product is also a raw material, so cannot appear in both tables. (Let's say we make cookies and we make ice cream, and we also make ice cream with cookies on top as a third/separate finished product!) Should cookies have two records ('CookieFinished' and 'CookieRM') and can they be linked together so cost is determined before it's part of another? Is there a better way? Thanks in advance!

 

[Moniker]

Put raw materials and finished products in the same table, and use a lookup table to identify what it is.

Table: Material_Type

MaterialTypeID   MaterialType
1                Raw Material
2                Finished Product

Table: Materials

MaterialID   MaterialTypeID   Material
1            1                Cookies
2            1                Ice Cream
3            2                Cookies 'N Cream

 

[Watergirl]

Using the same table

Thanks for your fast response! The vast majority of raw materials are purchased from other sources - very few of our finished products use one of our finished products as an 'ingredient'. I need to keep our products separate (in one table) and raw materials in another.

In your example, 'cookies' are shown only as a Material Type 1 (raw material) but in fact, once made, they are also a Material Type 2 - a finished product. 'Cookies' are made up of raw ingredients and then sold alone, but our 'cookies' are also a raw material - one of the ingredients then used in other distinct finished products as well. It is not one Material Type ID or another, but both - and as such, cannot exist in the same table.

Also - is anyone aware of any inventory/cost of goods sold sample databases out there? It seems such a common or typical db need - I hate wasting time reinventing the wheel, or not addressing something up front that will later become necessary or apparent! Thanks again for the help.

 

[CraigDolphin]

Taking Moniker's solution and adding a category might be an answer.

Table: Material_Type

MaterialTypeID   MaterialType
1                Raw Material
2                Finished Product
3                Both

Table: Materials

MaterialID   MaterialTypeID   Material
1            3                Cookies
2            1                Ice Cream
3            2                Cookies 'N Cream

then to generate a list of products query for material types 2 and 3, for raw ingredients query for material types 1 and 3...

 

[Watergirl]

Selecting from two tables

I appreciate your taking the time to think about my problem, and to post!

If I add a third table for those few ingredients that are both a finished product and a raw material, I would have to add both tables to the subform that I hope will enable us to create the recipe, correct? Or is that not a big issue?

Do you see a problem ahead if I have the product occupy two tables, but give it distinct names in each? I'm leaning that way ao I can pull from it on the raw material table when it's going into a recipe as an ingredient, including its associated cost of goods, and also list it with our product costs of goods sold from the finished products table.

I know the object is to not repeat information in two places, but technically - it's not exactly the same item much the same way a customer can also be a supplier and exist on two different tables. ??

 

[CraigDolphin]

Sorry. I don't see that adding a third table will help you at all and it will create headaches for you down the line IMO.

My suggestion was to add a third category to Moniker's Material_Type table, and to have a single table for Materials that uses a field to describe what category a material falls into (product only, supply only, or both).

To generate a list of products you use a query as the recordset for your 'products' form that shows records in table materials WHERE the field MaterialTypeID is either equal to 2 or 3.

To generate a list of supplies then you use a different query as your recordset for your 'supplies' form (or for a combo box, or whatever). This query would show the records in table Materials WHERE the field MaterialTypeID is equal to 1 or equal to 3.

If you want to generate a list of materials that are both 'supply' and 'product' then use a query to show the records in table Materials WHERE the field MaterialTypeID is equal to 3 only.

Splitting the Materials table into two tables is going to make tracking quantities of the product and supply materials much more tricky IMO. Even combining the records from the two tables into a list of products and a list of supplies becomes much more complicated.

 

[Watergirl]

One table for all materials

First of all, I apologize - I did indeed 'see' third category, but 'heard' third table.

I thought breaking the two types of products (two separate tables, raw and finished) further clairified the items, but I can certainly see the beauty of one (albeit huge!) table with all of products and just let the queries do the discriminating!

Thanks so much for your patience, Craig.

 

[CraigDolphin]

Glad to help. And don't worry about how 'huge' the table will be. Access is designed to handle tens/hundreds of thousands of records in tables/queries as long as the number of fields in a table doesn't get too large.

 

[Moniker]

Correct. A "tall" database design (a lot of records in a few fields) is much better than a "fat" database design (one with a ton of fields). I have tables that have literally millions of records in them, which is fine. The more important part (and what you try to avoid) is adding fields that aren't necessary, contain repeating data elements, etc.

In the example I made that Craig later modified a little, you would not want to store the words "Raw Material" or "Finished Product" in each record. You want to store "1" or "2" and lookup those values as necessary. The reason for this is that "Raw Material" is 12 bytes, "Finished Product" is 16 bytes, and a "1" or "2" is just one byte. It's not that big a deal initially, but if you end up with 100,000 Raw Materials and 25,000 finished products, that field stores 1,200,000 bytes (Raw Materials 12*100,000) plus 400,000 bytes (Finished Product 16*25,000) for a total of 1,600,000 when it can be storing just 125,000 bytes.

 

[Watergirl]

Tall is better than fat!

Makes SUCH good sense to me (now)!

I may be back with more questions as I start putting my design to work. Much thanks to you both.