Using circular references

[bismuth83]

I'd like to have a recursive query that refers to itself, but I have
run into the 'circular reference' error. Is there a workaround
that'll let me do something similar?

As a test for a larger version, I have a table [TestRecurse] which
works as a nested set (see http://www.intelligententerprise.com/001020/celko.jhtml?_requestedid=697912):
ListID Value lft rgt
1 0 1 18
2 0 2 9
3 0 10 17
4 1 3 4
5 2 5 6
6 3 7 8
7 0.4 11 12
8 0.5 13 14
9 10 15 16

I also use a query [TestRecurse_ListedChildren] based on this table
which lists any parents and their children:
ParentID ChildID
1 2
1 3
2 4
2 5
2 6
3 7
3 8

Then the circular reference query [TestRecurse_Formula] below:
SELECT
TestRecurse.ListID AS CurrentID,
TestRecurse_ListedChildren.ChildID,
nz(
(SELECT Count(TestRecurse_ListedChildren.ChildID) AS TotalChildren
FROM TestRecurse_ListedChildren
WHERE TestRecurse_ListedChildren.ParentID=[TestRecurse].[ListID]
GROUP BY TestRecurse_ListedChildren.ParentID)
,0) AS NumChildren,
Sum(
IIf(
[NumChildren]=0,
[TestRecurse].[Value],
(
SELECT top 1 Result
FROM TestRecurse_Formula
WHERE CurrentID=TestRecurse_ListedChildren.ChildID
)
)
) AS InitVal,
[InitVal]*3 AS Result
FROM
TestRecurse
LEFT JOIN TestRecurse_ListedChildren
ON TestRecurse.ListID = TestRecurse_ListedChildren.ParentID
GROUP BY
TestRecurse.ListID,
TestRecurse_ListedChildren.ChildID;

Circular reference is caused by the 3rd argument of the iff()
statement. But I think my joins are wrong as well, since the query
above would have duplicating parents. I've removed the duplication
and listed ideal results below:
CurrentID NumChildren InitVal Result
1 2 50.7 456.3
2 3 18 54
3 3 32.7 98.1
4 0 1 3
5 0 2 6
6 0 3 9
7 0 0.4 1.2
8 0 0.5 1.5
9 0 10 30

Hopefully that's not too confusing. Hope anyone can lend some ideas.
Thanks!

 

[Michel Walsh]

I don't think you are using MS SQL Server 2005, or later, since you use iif.

With Jet, you cannot use recursion. You CAN refer to the same table more
than once, though, with alias, a little bit like two fingers can 'bookmark'
two different lines of the same single list (table). That is not the same
thing as recursion. What are you trying to solve (rather than how you try to
solve it) ?




Hoping it may help,
Vanderghast, Access MVP

 

[bismuth83]

Thanks, I'm only using Access 2003.

I may be trying to oversimplify the situation, but I have a list of
values that combine to produce variables for another set of formulas,
and these combine as variables into another set of formulas. Been
trying to avoid having to write VBA functions for the sake of
performance speed. But I might end up using a fixed number of levels
(and multiple copies of the same query, which I was also trying to
avoid) for these functions, if I can't find any alternative.