Combine values from many table rows into one string value

' Concatenate one field grouped by another field from a table or query.
'
' Usage:
'   Source:     Table or query to read records from.
'   Field:      Fieldname holding values to concatenate.
'   Group:      Fieldname to group by.
'   Value:      Value from query to group by.
'   Separator:  The separator between the concatenated values.
'
' Example:
'   SELECT
'       table.ManagerID, table.Name, ConcatenateField("table","Area","ManagerID",[ManagerID]) AS Areas
'   FROM
'       table
'   GROUP BY
'       table.ManagerID;
'
' To clear cache:
'   ConcatenateField("","","",Null)
'
' 2016-10-17. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function ConcatenateField( _
    ByVal Source As String, _
    ByVal Field As String, _
    ByVal Group As String, _
    ByVal Value As Variant, _
    Optional ByVal Separator As String = ";") _
    As String
    
    ' SQL to retrieve the field to concatenate and the field to group by.
    Const BaseSql   As String = "Select [{1}],[{2}] From [{0}] Where [{1}] Is Not Null Order By 1 Asc"
    
    Static Records  As DAO.Recordset
    Static Rows     As Variant
    
    Dim Fields()    As Variant
    Dim Sql         As String
    Dim Item        As Long
    Dim Index       As Long
    Dim ItemList    As String
    
    If Source = "" Then
        ' Clear cache.
        Rows = Null
        Exit Function
    End If
    
    If IsNull(Rows) Or IsEmpty(Rows) Then
        ' Finish SQL to call.
        Sql = Replace(Replace(Replace(BaseSql, "{0}", Source), "{1}", Field), "{2}", Group)
        
        ' Retrieve all records from table or query.
        Set Records = CurrentDb.OpenRecordset(Sql, dbOpenSnapshot)
    
        If Records.RecordCount > 0 Then
            ' Fill array from all records.
            Rows = Records.GetRows(Records.RecordCount)
        Else
            ' No records.
            Rows = Null
        End If
        Records.Close
        
        Set Records = Nothing
    End If
       
    If Not IsNull(Rows) Then
        ' Filter and convert array to one dimension.
        Index = -1
        For Item = LBound(Rows, 2) To UBound(Rows, 2)
            If Rows(1, Item) = Value Then
                ' This row belongs to the group.
                Index = Index + 1
                ReDim Preserve Fields(Index)
                Fields(Index) = Rows(0, Item)
            End If
        Next
        
        ' Create concatenated value list from array of values.
        ItemList = Join(Fields(), Separator)
    End If
    
    ConcatenateField = ItemList
    
End Function

Select all Open in new window

SELECT
   GenericItem, Posn, Item, ConcatenateField("tblGenericBOMMatrix","Option","GenericItem",[GenericItem]) AS Options
FROM
   tblGenericBOMMatrix
GROUP BY
   GenericItem, Posn, Item;

Select all Open in new window