In situations where a Parent record has a limited number of children (0 to 10 works well) and you would like to list those children next to the parent somewhat like you would with a Pivoted table. Pivoting the table would however result in a massive table which is extremely wide. Pivoting and concatenating the fields can keep the resulting list within a manageable width. Take for example the following table.
So we have table with four header records and the children all relate to those parents. For each parent obtain a list of children
Firstly place the following function in a module
Public Function Conc(Fieldx, Identity, Value, Source) As Variant
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
vFld = Null
SQL = "SELECT [" & Fieldx & "] as Fld" & _
" FROM [" & Source & "]" & _
" WHERE [" & Identity & "]=" & Value
' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
' concatenate the field.
Do While Not rs.EOF
If Not IsNull(rs!Fld) Then
vFld = vFld & ", " & rs!Fld
End If
rs.MoveNext
Loop
' remove leading comma and space.
vFld = Mid(vFld, 3)
Set cnn = Nothing
Set rs = Nothing
' return concatenated string.
Conc = vFld
End Function
Now set up a Query and call the function in an expression
This results in the appropriate list
