queries and alias

[cc5305]

I have a table which contains info about account members. But the db hierarchy is that this table has an id-key field for individual identification, it also has another id field that identifies the "parent" object, which is also stored using the same table. So to find the number of end children a parent has, I need to make an alias to compare the parent's own id-key field and the childs's parent object id field. However, some childdren object has its own children as well.
How can I get the total number of end children each parent has regardless of the number of "child" level exists between the end children and the "parent"? [sig][/sig]

 

[MichaelRed]

This could be hazardous to your (database) health!.

First, you need to GUARNTEE there are no 'circular' references (e.g. Parent(A) has Child (B) who has Child (parent A). If this happens, then obviously the list/chain is infinite and can NEVER be complete.

If - the above - can be GURANREED to include no circular references - then:

I would suggest that you normalize the database/table. This invloves even more 'research'. If the number of levels of parent-child is "small" - i.e. less than a few - you could setup the series of child tables, using a [ParentID] link at each level.

If you get this far, a simple series of queries will provide the listings requested. Assuming you were going 'top-down', a first query would select the desired top-level Parents and their ChildId's. A next level of queries would layer on the frist to select all of the ChilId's in the first child level, a next level ...

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[cc5305]

Then how can I make sure that I don't double count the &quot;parent/child&quot; in between the top parent and the bottom child? BTW, there are only three levels. [sig][/sig]

 

[MichaelRed]

IF the normaliziation is done correctly, there will be no duplicates in the child tables. You can check this by doing the classic &quot;find duplicates&quot; query available through Ms. Access Query wizzard.

In building the various Parent/Child tables, you may set up the queries to include only unique records. This would be the usual process, however individual situations may dictate otherwise. If you are not doing unique records from the current table to create the normalized parent/child tables, there will probably be some additional issues to resolve before your list is correct.

I suggest that you try to get the parent/child tables created and check them for duplications (in the ID Fields). Then look at the next step.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]