I need to roll up the costs in a bill of materials using VBA in Access 2007. I have a table with a bill of materials like the one below. The list is in the correct order, meaning that items in rows 8 and 9 (level 4 items) both roll into row 7 (the level 3 item directly above it). I also have several with no costs just like below. So rolling row 8 and 9 into row 7 should result in a $0 value in the "Rolled_Cost" column for line 7. (It would be 3*0 + 15*0 = 0).
Has anyone done something like this before? I haven't been able to find anything online.
Sequence Parent_Number Item_Number Level Quantity_Per Cost Rolled_Cost 1 a 1 1 300 2 a b 2 3 600 3 b c 3 20 400 4 c d 4 .004 5 c e 4 1 6 b f 3 .5 7 b g 3 .5 200 8 g h 4 15 9 g i 4 3 10 b j 3 1 300