Accessing hierarchies

[Sajid]

Hi experts

I have a familyTree table which looks like this in MS Access 2000

id Name parent
1 A 0
2 A1 1
3 A2 1
4 A3 1
5 A11 2
6 A12 2
....................................
....................................

I can access only upto only level with a recursive relationship query. How could it be possible to retrieve all the children and grand children and so on upto n levels of a parent. The parent id should be provided as a parameter for the query. Also another query should be able to retrieve the parent, grand parent and so on upto level for a child. The queries should also return a level field (calculated at runtime, not stored in the database). Is it possible in MS Access.

Regards

Sajid

 

[Tom Ellison]

Dear Sajid:

Are you really doing a family tree, or some similar application? This
can be important. There are many variations on tree hierarchies, so
it is important to be considering those variations.

I'd rather not be too explicit, but it is possible for a person to be
both father and grandfather to a child. If you have the need to model
such things, you must have rules about how the level number is
assigned in this case.

One reason I suspect you may not be modeling a real family tree is
that you do not record the gender of the persons. You would normally
expect to see both male and female, with each person having two
parents. But perhaps you're modeling only the male lineage.

Now, to perform this "without limit" to a fairly high number of levels
requires recursion. I don't think you will get that using Access Jet.
If you are using MSDE, there are some applicable techniques.

The alternative would be to build a recursive function, using
recordsets for the source table, and putting the results in another
temporary table. This will be relatively slow, so the size of the
source dataset should be considered before spending the time to build
it.

Please get back with some answers. I'll try to fill in details after
I have enough answers.

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

 

[Sajid]

1) Yes you are right. It is not an actual family tree, but it resembles to that.

2) A person cannt be father as well as grand father to the same child

3) Well it doesnt matter whether the "node" is a male or female

4) I'm going to use this database for a website. But how long could it take to retrieve a tree from a total of say 100000 nodes.

Regards

 

[Tom Ellison]

Dear Sajid:

I only see one question here. "How long could it take to retrieve a
tree from a total of say 100000 nodes?"

That would depend on the number of levels you have. If all 100,000
are up a single lineage of 100,000 levels, then you should live so
long. If all are 1 parent with 99,999 children, it will take a few
seconds. Proper optimization and the power of your processor and hard
drive are important factors.

How many levels are you expecting?

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

 

[Sajid]

Thank you Tom for your interest in my problem

Actually I'm developing a Multi Level Marketing site. There is only one person at the top, usually the site owner(admin). Other members register and come under the admin and become its child. Now each child of the admin performs its own work and refers some other members and in this way they become the children of admin's children. In this way the tree grows. Every week, the site administrator retrieves the whole tree from the database, and assigns some points to each member depending upon how deep his/her tree is.

Therefore I need some function that should retrieve only the tree of a specific member. Function must return all the members under that member + the level at which each member is according to the parent.

The function must perform the same way as the connect by clause in Oracle performs or online article "Accessing Hierarchies" for SQL Server

I'll be very thankful

Regard
Sajid

 

[Tom Ellison]

Dear Sajid:

This is a much different type of problem. I'm going to suggest a
"level constrained" tree approach with "no level skipping" and use a
union query. You will need to choose a maximum number of levels at
which it can work. Ten would be fairly easy, or as many as 20 if you
really think you'll need it. Please let me know how many you'll need.

From that point, it won't be that difficult to build. That is, trees
are a bit of work, but for a tree it isn't that bad. But this way we
can scrap the recursive bit and the VBA.

I cannot comment on your Oracle specification. Please post a link to
the "Accessing Hierarchies" article. Are you going to be using SQL
Server, or just want to use something from that approach? Jet cannot
do everything SQL Server does, so you might want to switch if that's
an absolute requirement.

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

 

[Sajid]

Hi Tom

Thanks again for your concern.

Well if we have to specify the maximum number of levels than please keep it upto 20.

The procedure that works with SQL Server is "Expanding Hierarchies" from:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_5yk3.as

Regard

Sajid