Hi Guys.
Happy 2017!
I tried to repurpose an old thread ID 149001 (sorry cant post links yet) but got lost in translation.
The principle is the same. You have ingredients --> component --> finished good.
With just these three levels, I could create three queries and the costs could roll up. The problem is when you have a component which could be made up of a component and some materials. So when I change the price of a raw material, I want all the costs to roll up.
Could someone help me with this please. I have spent several hours and several attempts at repurposing that example and its driving me potty!
The tables are as follows:
BOMs - This contains a list of all the Finish Good and Component Bill Of Materials and the qty required
RAWSKUs - This contains all the materials and ingredients and their cost (field StdCostGBP).
CommodityCodes - This is a little annoying but we use the word Commodity Code for classifying the type of SKU, i.e. whether its a finished goods, packaging, component etc. This is because there was a field called Commodity Code in the stock management system and work used it for classification instead. I would like to use field (CommodityCode) when doing the calculations as they are shortcode and do not contain spaces. In my VB googling, when spaces are involved and _ is used, I cant understand it!
CommodityCodeDescription - This table includes all the different codes and their description.
FGRSPs - This contains a retail selling price. Finished goods have an extra "overhead" based on a 5% of the RSP. However, I suppose an easier way would be, if the master SKU is in this table then take the RSP and multiply by 5%. That would make it future proof
LabourStandards - Finished Goods and Components need processing or packing costs and this table has the value and the qty of each.
I have created an OutputTable which shows what the values should be.
This is not a BOM database in the sense it will be a master SKU storage. What I need this for is to check all the existing costs which are on a spreadsheet are correct. So essentially, I want to load all the BOMs and press a button and let it sit and calculate everything and then check against what has been manually created over a few years.
Now clearly some of the hand created standard costs will be wrong so what would be create is the ability to add the raw material in twice with two different prices and have the DB use the latest price. The same for the other tables with the dates (FGRSPs, LabourStandards, RawSKUs). BOM's will only be in there once. The date was added there so I know when I loaded in the BOM. In case someone changes it and doesn't tell me!
The multiple dates would be great but not essential. I don't mind manually changing a SKU and then running the magic button and checking the output tables in Excel.
Attached is the DB. Really appreciate the help!!
Happy 2017!
I tried to repurpose an old thread ID 149001 (sorry cant post links yet) but got lost in translation.
The principle is the same. You have ingredients --> component --> finished good.
With just these three levels, I could create three queries and the costs could roll up. The problem is when you have a component which could be made up of a component and some materials. So when I change the price of a raw material, I want all the costs to roll up.
Could someone help me with this please. I have spent several hours and several attempts at repurposing that example and its driving me potty!
The tables are as follows:
BOMs - This contains a list of all the Finish Good and Component Bill Of Materials and the qty required
RAWSKUs - This contains all the materials and ingredients and their cost (field StdCostGBP).
CommodityCodes - This is a little annoying but we use the word Commodity Code for classifying the type of SKU, i.e. whether its a finished goods, packaging, component etc. This is because there was a field called Commodity Code in the stock management system and work used it for classification instead. I would like to use field (CommodityCode) when doing the calculations as they are shortcode and do not contain spaces. In my VB googling, when spaces are involved and _ is used, I cant understand it!
CommodityCodeDescription - This table includes all the different codes and their description.
FGRSPs - This contains a retail selling price. Finished goods have an extra "overhead" based on a 5% of the RSP. However, I suppose an easier way would be, if the master SKU is in this table then take the RSP and multiply by 5%. That would make it future proof
LabourStandards - Finished Goods and Components need processing or packing costs and this table has the value and the qty of each.
I have created an OutputTable which shows what the values should be.
This is not a BOM database in the sense it will be a master SKU storage. What I need this for is to check all the existing costs which are on a spreadsheet are correct. So essentially, I want to load all the BOMs and press a button and let it sit and calculate everything and then check against what has been manually created over a few years.
Now clearly some of the hand created standard costs will be wrong so what would be create is the ability to add the raw material in twice with two different prices and have the DB use the latest price. The same for the other tables with the dates (FGRSPs, LabourStandards, RawSKUs). BOM's will only be in there once. The date was added there so I know when I loaded in the BOM. In case someone changes it and doesn't tell me!
The multiple dates would be great but not essential. I don't mind manually changing a SKU and then running the magic button and checking the output tables in Excel.
Attached is the DB. Really appreciate the help!!
