Implementing a Hierarchy in MS Access

[Flavelle Ballem]

Given a table with the following:

ID : int (autonumber)
ParentID: int (nullable)
Information: string
....

and a self-join, where if a record has a Parent, then the ID for the Parent
is contained in ParentID,

I would like to write a query that will 'walk up the tree' - adding
information from all parents in order until (and including) the Parent that
does not have a Parent.

for example:
ID ParentID Information
1 null Record 1
2 null Record 2
3 1 Record 3
4 3 Record 4

I would like to be able to make a string reading "Record 1, Record 3, Record
4" for Record 4 by walking up the tree to Record 1.

Suggestions would be most welcome.

Many thanks,
Flavelle

 

[Stefan Hoffmann]

hi Flavelle,

and a self-join, where if a record has a Parent, then the ID for the Parent
is contained in ParentID,
I would like to write a query that will 'walk up the tree' - adding
information from all parents in order until (and including) the Parent that
does not have a Parent.

This is not possible in Access. You can use a procedure or you may look
at nested sets.


mfG
--> stefan <--

 

[Flavelle Ballem]

What is a 'nested set'. I am familiar with procedures (which I assume I
would create in a global module so that I can use it in a query).

Thanks,
Flavelle

 

[Peter Yang[MSFT]]

Hello Flavelle,

It's not feasible to use a query to do this in Access. However, based on my
test, you could try to use VBA code to achieve this goal in Access. I
suppose the table name is parentchild in your database, you could create
following function in a module to test:


======

Function getlist(info As String)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String
Dim result As String

Set db = application.CurrentDb

sql = "select * from parentchild where information ='" & info & "'"
'DoCmd.runsql ("select * from parentchild")
Set rs = db.OpenRecordset(sql)

result = rs("information")

While (rs("parentid") > 0)
sql = "select * from parentchild where id = " & rs("parentid")
Set rs = db.OpenRecordset(sql)
result = result & " " & rs("information")
Wend

msgbox (result)


End Function

=====

In Immediate window, you could type following to test your function.

? getlist("record1")

Hope this is helpful. Please feel free to let's know if you have any
comments or questions.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Stefan Hoffmann]

hi Flavelle,

What is a 'nested set'.

A nested set is a structure, which makes it possible to read entire
branches with one statement (avoidung recursion). In situations where
you have far more reads than updates, this may be an interesting type of
structure.

http://www.mvps.org/access/queries/qry0023.htm
http://www.intelligententerprise.com/001020/celko.jhtml
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html


mfG
--> stefan <--

 

[Peter Yang[MSFT]]

Hello Flavelle,

I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.