The programming technique used to return "unlimited generations" in
this kind of situation is called recursion. And even this technique
does not, in practice, work for an unlimited number of generations, as
it must store the layers on a stack to be able to return to them
later. Thus, it has a limitation based on the amount of memory in the
computer. But at least this can be a very high limit.
Before you can approach using this, you should be prepared to
demonstrate that the parent relationship creates a tree structure in
the database. If the database does not contain a single, connected
tree and no cycles (either directed or not) then several bad things
may happen. The worst of these is that the program traversing the
data will get into a loop that would not end unless a way to detect
this situation is also programmed.
The way to avoid having an endless loop when the data is bad can be
just to limit the number of generations allowed. But once you've done
that, you're back to the solution you just rejected - one that works
for a limited number of generations.
If you want, we can discuss how to structure the data to prevent its
not being a tree structure (that is, to avoid errors in data entry
that will only cause invalid results).
Performing a traversal on a recursive basis can be performed using a
VBA module. It would need to create a temporary table showing a
traversal of the tree (pre-, post-, or end-order traversals).
I have looked for, but never found a way to write a query that is
recursive. I seriously don't think this was a design goal of those
who defined the SQL standards. If anyone knows otherwise, I'd really
appreciate hearing of that.
Because of that, a finite generation query system is probably the best
query based solution.
Thanks for the tip, but the example seems to be limited to the number of
times you copy the table in the query.
I'm after something that will return unlimited generations of parent and
child records (Parent, Child, Grandchild, GreatGrandchild,
GreatGreatGrandchild, GreatGreatGreatGrandchild and so on...) I suspect that
some kind of recursive query that ends when no values are returned may be
needed.
Thanks again,
Trev.
Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
