Recusive multiplication

[brian rich]

I'm building a parts system which needs to include a Bill of Materials for each part. My current solution has a Parent table which includes every part, which is linked to a sub assembly table which specifies the ID of the parent part in the parent table, and the ID in the parent table of the subassembly part, and the quantity needed. For example, Part A uses 2 of part B. Part B uses 1 of Part C and 4 of part Part Z

I'd like to be able to build a BOM List using this relationship which, for any record in the parent table, gives the list of parts required and the total quantity of each part. This means that the BOM for part A will show 2 of part C and eight of Part Z.

I've got as far as defining a calculated field in the parent table which recursively points to itself in the sub-assenbly table until the subassembly table returns nothing. This works quite nicely and I can create a list of all the basic parts. What I can't work out is how to multiply quantities recusively. If parent part A uses 2 of Parent part B, which in turn uses 4 of part C, I'd like my recusive list to show that part A needs 8 of part C but I can't see how to do this.

It may be that this isn't the best way to represent the part structure, but I can't see how else to do it.

Can anyone help?

Thanks

Brian