Recursive query to find all children, grandchildren, great etc...

[thenighthawk]

Hi All,

Table 1 has a list of items with two key fields, ID and Parent
Parent refers back to ID


Table 2 has a list of five integer values of interest for some of the
items in table 1, call them i1 - i5, as well as a foriegn key
relationship back to ID on table 1, fID

Ideally, I'd like to produce a sum of all the table 2 data for all the
children of ID from table 1.

I know that this will have to be done in vba, since recursion can't be
done as a query.

I guess the first ideal step would be a suggestion on how to build an
array or recordset with just all the children/grandchildren/great
etc... grandchildren.

Thanks!

 

[Allen Browne]

You're right. The SQL language does not handle this kind of thing well.

There's a basic query example in this link that goes to 4 generations:
Self Joins: tables that look themselves up (Pedigrees example)
at:
http://allenbrowne.com/ser-06.html

There's a more detailed one with a downloadable example here:
Bill Of Materials
at:
http://www.mvps.org/access/modules/mdl0027.htm

A major issue is the chance of infinite recursion, where one of the children
is an ancestor of itself. So whenever, I have had to work with this kind of
data, I insist that a maximum depth is defined (e.g. 5 or 8 generations.) If
the data does not resolve in that depth, we give the user the info as to
which record(s) present the problem and ask them to fix it before proceeding
further.

This is done with a temp table that has a field for each generation, and
contains the unrecursed data i.e. tracing the generations. The VBA code that
populates that table loops through the data and does an AddNew to this
"resolved" table. It's quite long, messy, and generally specific to the
database, so I'm not sure how helpful an example would be.

Sorry: I don't think there's a simple, quick, generic approach for this kind
of data.

 

[thenighthawk]

Thanks for the pointer Allen!

Right now at least, the data's clean so that it won't have any
self-references... the table is used right now to build a Treeview, and
that works properly...

 

[Michel Walsh]

Hi,


If you have MS SQL Server 2005, you can build up recursive SQL (and
limit the level of recursion). A passthrough query (from Jet) could run the
SQL 2005 statement on what SQL Server would get the data as a linked server
(since Jet owns the data). The setting is a little long to describe, and if
you don't have SQL 2005, it won't help. But if you have access to SQL 2005,
just reply here, and I will see what details I can supply, if what I
mentioned is not evident to you.



Hoping it may help,
Vanderghast, Access MVP