hierarchical view 1

[striker83716]

Hi all and thanks for all of the wonderful help I have received in the past.

I have a complex problem and will try to describe it as well as possible.

I have exported a Engineering Bill of materials out of JDEdwards into Access. The BOM structure from JDE is in a hierarchy order, meaning it lists the top level assembly, it's sub-assemblies, etc. I would like to display this in access in the same type of hierarchy format, in both reports and in forms. Here is the problem, The table is structured with the part number and a "level" i.e. The "child record" does not identify the parent only it's level in the BOM and the BOM is exported in the proper sequence.

pn level
200 1
150 .2
500 .2
600 .2
550 ..3
400 1
150 .2

The way that I would like to show this is

200 parent
150 child
500 child
600 child
550 grandchild
400 parent
150 child

Any help would be greatly appreciated.

 

[PHV]

Something like this ?
SELECT String(2*(Len(level)-1)," ") & pn As IndentedPn

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[striker83716]

This works great! Thank You for your help

Rob

 

[striker83716]

Although this worked for what I originally asked for I now need to approach it slightly differently. I need to make this a true parent child relationship type database. Can you help me with this? I need to create a "make table" query that takes the original data, and creates a new field with the parent of the current record. original table is above, and this would be the result of the query..
pn level Parent
200 1
150 .2 200
500 .2 200
600 .2 200
550 ..3 600
400 1
150 .2 400