How to find "chained" connections without VBA

[Alc]

I know how to do the following using VBA, but I've been asked if I can create a query to achieve the same results. In case one of the resident geniuses here has any ideas, I thought I'd ask.

I have a table containing a number of cases, each having a unique ID.
Some cases are stand-alone, others have "Parent" or "Child" cases, some can have both.
For example
Case 1 - no parent or child cases
Case 2 - no parent or child cases
Case 3 - no parent or child cases
Case 4 - created by Case 3
Case 5 - no parent or child cases
Case 6 - created by Case 4
Case 7 - created by case 6
Case 8 - created by case 6

I want to be able to enter a Case ID and return all relevant case numbers, whether the connected cases are Parents or Children of the entered ID. Ideally, I'd also like to know where in the hierarchy my entered ID falls.

In the above example, if I enter ID 6, I'd want to see something like:
Case 3 - Original
Case 4 - Parent case 3
Case 6 - Parent case 4
Case 7 - Parent case 6
case 8 - Parent case 6

Can this be done using just SQL and without the use of VBA?

 

[arnelgp]

you need to create another field, for the id of parent case.
mere description of which case belongs, need additional function.

 

[Alc]

Thanks for the reply.

Unfortunately, this data comes from a linked table on a database over which I have no control. I can't make any changes to any of the tables within it.

 

[arnelgp]

then it would be hard to just use a query.
a function will do that for you.

 

[plog]

It's possible with SQL if you have a small number of possible levels. Your example went 4 levels deep, is that as far as they possibly could go? If more I'd make a function.

To do it with SQL you simply keep LEFT JOINING your table to itself until you reach the necessary depth. Then with some UNION trickery you can produce one query that displays your data. Again though, I wouldn't try this deeper than the 4 levels you have--each level deepe makes much more difficult.

 

[MarkK]

Can this be done using just SQL and without the use of VBA?

No, a hierarchical result, or tree, cannot be returned by SQL. Yes, you could write SQL that returns a fixed number of levels or branches from a tree, but then you could always add a new level to the tree that exceeds the scope of your SQL.

The simplest tree navigation algorithm is a recursive function, and that would require VBA in Access.

 

[arnelgp]

it traces the root, then the descendant.

 

[Alc]

Thanks to all.

There's no limit to how many levels there can be, so that's a problem.

On the one hand, I was hoping to find some expert idea I hadn't thought about. On the other, it's nice to have my initial suspicions confirmed.

A function using VBA it is

 

[The_Doc_Man]

Unfortunately, this data comes from a linked table on a database over which I have no control. I can't make any changes to any of the tables within it.

Your description implies that you have a "parent" pointer which is empty for any parent case but is populated for child cases. Otherwise, you could never say

Case 7 - Parent case 6
case 8 - Parent case 6

because you would have no way to record parentage. Therefore, you have at least a starting point, however rudimentary. The problem you have is that to see what you are doing absolutely REQUIRES recursion for the general case. If you really wanted to go with this entirely in a query, I'm taking bets with good odds that this will be a seriously non-trivial problem.

You are, in essence, doing what is called a "topological" sort, which is to say that the topology of your table, i.e. its relationships within itself, determines the sort order. This is the same problem used by scheduler software such as MS Project.

If the externally linked table doesn't have sufficient infrastructure to support your action, you might have to take a more radical approach. You might have to make a temporary table into which you copy the records of the case table and add a few extra fields to support the oddball sort. Then report on the temporary table before erasing it.

You also have to consider multi-layered parents and siblings since it would apparently be possible to have two children at the same generation. In your example, your 4th generation has siblings. But would there be anything to stop you from also having a 3rd generation sibling that didn't preclude one of those 3rd-gen sibs having two 4th-gen child entries.

Now, the simplest part of your question: To have a single parent/child relationship showing both at the same time, you need TWO references to the same table and define a relationship such that the parent pointer of the child table reference selects the PK of the parent table reference - even though they are both the same table.

You can do this in the relationship screen or in the upper section of a query grid that has the blank part on top and the cell grid on bottom. Add the same table name twice. The second time, it creates an icon with the name plus a parenthetical number. So you can build a relationship between the child table and its parent as a one parent/many (or no) children. That is your starting point. After that, recursion rears its ugly head.

The "sequence of left joins" mentioned by PLOG would be based on adding the table multiple times and setting up the relationship for each added instance of the table reference. Then you would need to consider special names for each level's field names since they would otherwise overlap and confuse the hell out of you.

I don't want to disappoint you because this IS something that could be done - but you first need to fully define how deep in recursion you will go and how you want to see your reports to reflect the hierarchy of searched fields and multi-generational cases. It all has to start with the report format.