Roll up costs with vba

[nigelbloomy]

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

[nigelbloomy]

I think I can do a query for the item numbers with the highest level, multiply their quantities with their cost, and put that result into the rolled cost for the parent number. Then I can move to the second highest level and do that again.

I just don't know what the VBA code would look like to do that.