Retrieve records, and all records joined to those records, etc., e

[Peter Stone]

Access 2002-2003, XP

I have a table tblRec with a self join using table tjnRecPtof (two fields
RecID and PtofID). I want to retrieve all records joined to a particular
record (e.g. PtofID =7), and then all records joined to those records, and
then all records joined to those records, etc., etc.

Thank you

 

[Tom van Stiphout]

On Wed, 10 Jun 2009 16:47:01 -0700, Peter Stone

Alas, Access can't do that, afaik. This requires support for recursive
queries, which SQL Server first implemented in version 2005 but Access
may never support this.
Next best ideas include:
* Put the same table on the query several times, properly linked, and
hope that the relations don't go deeper than your number of tables.
* Write some VBA code to recursively populate a "temporary" table, and
go from there. I have done this successfully for an app where we truly
did not know how deep the nesting could be. Not very fast, but it
works reliably.

-Tom.
Microsoft Access MVP

 

[Peter Stone]

On Wed, 10 Jun 2009 16:47:01 -0700, Peter Stone

Alas, Access can't do that, afaik. This requires support for recursive
queries, which SQL Server first implemented in version 2005 but Access
may never support this.
Next best ideas include:
* Put the same table on the query several times, properly linked, and
hope that the relations don't go deeper than your number of tables.
* Write some VBA code to recursively populate a "temporary" table, and
go from there. I have done this successfully for an app where we truly
did not know how deep the nesting could be. Not very fast, but it
works reliably.

-Tom.
Microsoft Access MVP