Hello guys, i have a bill of materials (BOM) list. and there are many levels to it. For example level 0,1,2,3,4 and so on. it is imported from excel to access in the tblDataSheet.
i have made a query called qryDataSheetUpdate to make some calculationsand there are forms for calculation purposes, frmCalculation and frmPurchasePriceUpdate.
what is required:
in the bill of material i when a new calculation is made or when a new BOM list is imported from excel, i want the values of the lower levels in the BOM list to be displayed as 0.
meaning: if the levels are 1,1,1,1,1,1,2,2,2,2,2 i want the value of all the 2's to be 0.
likewise, if it is 1,1,1,2,2,2,3,3,3 i want the value of all the 3's to be 0; the value of the last level 2 directly above the first level 3 to have the actual value, the first and second level 2's to have a value of 0.
the same thing for all the other levels. 1,1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,3,4,4,4,4,4,5,5,5,6,6,6,7,7
i need this because in the BOM list all the parts are calculated individually. hence each part is being calculated with its raw material price, treatment price, machining price. i don't want the prices to be repeated.
meaning: if there is a level 1,1,2,2,2. the 2's are the same part. so if the treatment cost of the level 2's is 6, the price is being repeated for each individual part. meaning the whole level 2 should cost 6euros but now it is 18 euros. so the level 1 directly above the level 2's cost 18+euros instead of 6+euros. the cost is more than it should be.
attached is my database.
i have made a query called qryDataSheetUpdate to make some calculationsand there are forms for calculation purposes, frmCalculation and frmPurchasePriceUpdate.
what is required:
in the bill of material i when a new calculation is made or when a new BOM list is imported from excel, i want the values of the lower levels in the BOM list to be displayed as 0.
meaning: if the levels are 1,1,1,1,1,1,2,2,2,2,2 i want the value of all the 2's to be 0.
likewise, if it is 1,1,1,2,2,2,3,3,3 i want the value of all the 3's to be 0; the value of the last level 2 directly above the first level 3 to have the actual value, the first and second level 2's to have a value of 0.
the same thing for all the other levels. 1,1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,3,4,4,4,4,4,5,5,5,6,6,6,7,7
i need this because in the BOM list all the parts are calculated individually. hence each part is being calculated with its raw material price, treatment price, machining price. i don't want the prices to be repeated.
meaning: if there is a level 1,1,2,2,2. the 2's are the same part. so if the treatment cost of the level 2's is 6, the price is being repeated for each individual part. meaning the whole level 2 should cost 6euros but now it is 18 euros. so the level 1 directly above the level 2's cost 18+euros instead of 6+euros. the cost is more than it should be.
attached is my database.
