Subqueries & Culmination

[Aaron]

Hi, got a problem with a subquery - hope someone can help
me out!

Say I have a table. The important fields are ID, ParentID
& Height.

I have built a query with a subquery linked using ID-
ParentID.

So the resulting datsheet ID's looks something like this:

1
|-2
| |
| |-3
| |-4
|
|-5
|-6

I want to have culminative values of the height field
(short number). So that there is a field in record "1"
that has the total value of all its sub-records.

So in the case of:
| ID | ParentID | Height | CulminativeHeight |
----------------------------------------------
| 1 | - | 10 | 26 |
| 2 | 1 | 5 | 4 |
| 3 | 2 | 2 | 0 |
| 4 | 2 | 2 | 0 |
| 5 | 1 | 5 | 2 |
| 6 | 5 | 2 | 0 |

How can I achieve the CulminativeHeight Field? (It must be
available for all records, even those without child sub-
records will need a value of 0).

Anyone go any ideas? Cheers,

Aaron

 

[Tom Ellison]

Dear Aaron:

There are not recursive techniques available in Jet queries, but there
may be a finite way of approaching this problem, given a limited
number of levels of nesting. Your sample data has only 2 levels of
nesting, so I'll make my suggestion for that case. It can be extended
for more levels.

First, there needs to be a query that developes the list of nested ID
/ ParentID pairs. I will assume your data is not cyclical, but forms
a proper tree, or set of trees.

SELECT ID, '-' Height AS ParentID FROM YourTable
WHERE ParentID IS NULL

The above would give you the first row you show below, along with the
raw data for Height. However, it will show every row of the table
which, without a rule for which rows you want to omit, is reasonable.
Every row is a candidate for the root of a tree, or subtree of the
whole. So, I added the WHERE ParentID IS NULL. Perhaps this is what
you want.

SELECT ID, ParentID, Height FROM YourTable
WHERE ParentID IN (SELECT ID, FROM YourTable WHERE ParentID IS NULL)

The above gives you the rows with ID values 2 and 5.

SELECT ID, ParentID, Height FROM YourTable
WHERE ParentID IN (SELECT ID, FROM YourTable
WHERE ParentID IN (SELECT ID, FROM YourTable WHERE ParentID IS NULL)

If you put these three together in a UNION query (I think UNION ALL
would be best) and add sorting as desired, you should be able to come
up with what you describe except the last column. The running sum can
be performed in a report or it can be done in the query. The report
is easier and runs faster, but a correlated subquery on the above
query can do it if needed.

I went pretty fast here, and it covers a lot of ground. You may need
more information to build it, or perhaps not. Let me know how you do
with this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

Although I didn't initially want to "cap" the amount of
levels its no real problem & it works a charm! Thanks for
all your accurate & insightful advice, you really got me
out of a bind...

Aaron

 

[Tom Ellison]

Dear Aaron:

It's often hard to tell at what technical level to respond. The fact
that you could wade throught such a short summary of the methods I
prefer and comprehend shows both expertise and persistence. I hope
this will serve you well.

Do you see the reasoning behind constraining the data in this way. It
is meant to make the enforcement of the tree structure easy so you can
implement the kind of UNION query I suggested with confidence.

And, if there were a "cycle" in the data and you had a recursive way
to build the results, it would never complete. So the 4GL methods of
SQL code are in some way advantageous.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts