> there is a table with all Material (stock), each one has a number. Now, in a
> second table there is listed the material that is a part of the Material.
>
> For example:
>
> Material ABC
> Material BCD
> Material XXX
> Material OMV
> Material KOM
>
> Material OMV can be a Part of XXX and XXX a Part of ABC, of course the BCD
> can assigned to XXX, OMV, ABC.
>
> Now I would like to know what is the total weight of the main material, (total
> weight from abc + amount of bcd + amount of xxx
>
> ABC has 2 XXX, XXX has 2 OMV
> Now we have 1 X ABC, 2 times XXX and 4 X OMV, OMV weighs 0,5kg and so
> on
>
> The structure
>
> Table Mainmaterial
> PartNo
> Description
>
> Table Material
> PartNo of Mainmaterial
> PartNoA = assigned Material
> Amount (of PartNoA)
>
> Is there a simpler statement possible than my stored procedure (recursive)

While, I don't see any reference to a weight field for the material... my answer would be:

I don't see how you can avoid the SP, just minimize the number of times you use it.

Store the weight in new Part_Weight table, trigger to tag TableMaterial changes (Changed = True) when change any of the sub-parts are made, and then a SP (which would be called before you run any analysis/report, and/or run on a scheduled basis) to recalculate the weights of any Part that have changed sub-parts. In this way you have the weights and only do the recursive SP when absolutely necessary.


Sean