Sorry, David, I did not read your question clearly.
Yes, the solution will involve iterating through the parents recursively
until you reach the top of the tree. There are two ways to do that. One is
with a VBA function. It's not too difficult to write recursive code, but the
practical issue is the endless loop if a subassembly is in its own parent
line. To avoid that means keeping a trace (probably an array) of the p.k.
values, and checking that you have not already visited the same record in
this tree.
Actually, whenever I've had to do these, I've cheated. I specify a limit on
the number of levels deep that are permitted, and then create an temp table
that resolves each child and each parent/grandparent/great... so you can see
each ancestor of a part. This unnormalized table gives instant performance,
which is certainly not the case if you are constantly opening recordsets and
tracing levels.
The other approach is to try to do this in SQL. I started some research on
this years ago, but never actually followed a solution through as it looked
messy. Joe Celko's stuff sounded promising if you wanted to chase that
approach. Links:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html
HTH
