manufacturing breakdown to lowest level

[#1]

Here a new challenge for you all ...

I hope you can find a way to solve my problem.

Here are the data I have:

End-product (M)

     Part List:

          2x component A (M)

          5x component B (M)

          3x product C (P)

     Working Hours :

          10 hours on machine 1

          5 hours on machine 2

Component A:

     Part List:

          20x product J (P)

          15x product I (P)

     Working Hours :

          3 hours on machine 5

          1 hour on machine 6

Component B:

     Part List:

          10x product K (P)

          10x product L (P)

     Working Hours :

          2 hours on machine 7

(M) refers to Manufactured products

(P) refers to Purchased products       

The part lists for all products are to be found in 1 data table and the routings (how many hours on which machine) are to be found in another data table.

In this example you see only 2 levels, but some of my actual products have more levels. Does anyone know how I get the breakdown of the end product to the lowest level? The result should be something like this :

End product contains :

     - 40x product J (P)

     - 30x product I (P)

     - 50x product K (P)

     - 50x product l (P)

     - 3x  product C (P)

     - 10 hours on machine 1

     - 5 hours on machine 2

     - 6 hours on machine 5

     - 2 hour on machine 6

     - 10 hours on machine 7

Kind regards,

Jef

[#2]

Hi Jef

You may wish to consider looking at hierarchical tables.

The model I use for one of my datasets is a bit less complicated but can go down to 18 levels! Each level "inherits" additional properties from the levels below.

Have a read of it in the manual.

You would need to use the products as "nodes" then have a separate table referencing them with the hours on each machine.

You will then need a separate table of "parents" and "children". In the case above M would be the parent to A and B

Erica

[#3]

On the other hand there may be simpler solutions, how many levels are we looking at?

[#4]

Erica,

thanks for your reply.

I think the maximum would be 4 or 5 levels. But it is not so that all the end products have 4 or 5 levels. Some may have 2 or 3, or just 1!

[#5]

Hi Jef

See attached- this is not what you want but starts off the hierarchical calculations in the script , I am not sure how to get the additional calculations of multiplying up the machine hours etc.

Perhaps a start. Otherwise you would be looking at iterations in the script.

Erica