Subquery possible?

[Jason W. Martin]

A record contains PARTID and COMPONENTID fields. There could be multiple
records per part. For example:

PARTID COMPONENTID
030.11A 030.090
030.11A 030.091
030.11A 030.09A

Issue - components are records within the same table and may contain
multiple sub-components themselves. It's possible there could be several
levels.
We need to select all records for a specific part and all records for all
components and all their parts.

For example, given the PARTID above, we need a query to produce the
following:

PARTID COMPONENTID
030.11A 030.090
030.11A 030.091
030.11A 030.09A
030.090 010.11A
030.090 010.11B
030.091 <no components, field is blank but record must be selected>
030.09A <no components, field is blank but record must be selected>
010.11A 001.001
010.11B <no components, field is blank but record must be selected>
001.001 <no components, field is blank but record must be selected>

I'm thinking a DAO or ADO recordset to loop through all records until
COMPONENTID returns ull and append the PARTID to a temporary table. Hoping
for a query solution. Any help is appreciated.

Thanks,

Jason

 

[Allen Browne]

The solution you propose of processing the parent generations until you
reach the top of the tree (null parent) makes sense.

The advantage of this approach is that your VBA code can identify an endless
loop (e.g. where a part is its own grandparent.) While this generally
represents bad data, there's not an obvious way to set up a constraint that
prevents this from occurring.

What I've generally done with this kind of data is to insist that any part
can be resolve in a fixed number of generations. The code runs, populating
an unnormalized temp table with the various fields for the generations. If
there are any parts that don't resolve, you display a list and insist the
user deals with these before you go on. If there's no errors, you can even
use the unnormalized temp table for further processing.

If you don't want to use the VBA approach, Joe Celko has some info on using
nested sets here:
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

I haven't tried it, but I understand the latest SQL Server has some support
for recursion that Access doesn't have.