Concatenate hierarchy

[Steve]

Greetings:

I am attempting to concatenate and format a data set that has parents and
subordinates as follows:

Item Name Parent
1 Item 1
2 Item 2
3 Item 3 1
4 Item 4 3
5 Item 5 1

In this data set item 1 is the parent of items 3 and 5 and item 3 is the
parent if item 4. Items 1 and 2 have no parent items. I am attempting to
concatenate and format this data set and put the results in a text box in a
report as follows:

Item list:
1 Item 1
-3 Item 3
-4 Item 4
-5 Item 5
2 Item 2

I have been trying to modify Duane Hookom's code below but I do not know
how to make nested loops in this setting. Conceptually I would like to move
throug each record, determine if the record is a parent. If it is a parent, I
would like to initiate a nested loop that looks through the rest of the
records to look for subordinates and concatenate them with the parent. This
gets complicated though becuase, as shown in the hypothetical dataset, some
subordinates are also parents. Any help with this would be greatly
appreciated.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

 

[Mike Painter]

Greetings:

I am attempting to concatenate and format a data set that has parents
and subordinates as follows:

Item Name Parent
1 Item 1
2 Item 2
3 Item 3 1
4 Item 4 3
5 Item 5 1

http://www.databasedev.co.uk/self-join_query.html and I think self-join is
covered in help.