Combine values from many table rows into one string value

Gary Croxford
Gary Croxford used Ask the Experts™
Thank you for looking at my question,

I have embedded an image from an Access table that I want to query so that I end up with the options as a string. I want to end up with one line showing, in this case,
GenericItem, Posn, Item, Options showing VE01, 200, 3890602089,SKD0

BOMMatrix Table
Any help you can offer will be much appreciated
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Are you attempting to export the table contents into a CSV file? Access provides both manual and programmatic tools for that:
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Take a look at the article posted here

This is designed to do exactly what you are looking for.
Most Valuable Expert 2015
Distinguished Expert 2018
You could use this function which caches to rows to speed up the final query quite a lot:

' 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:
'       table.ManagerID, table.Name, ConcatenateField("table","Area","ManagerID",[ManagerID]) AS Areas
'   FROM
'       table
'       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)
            ' No records.
            Rows = Null
        End If
        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
        ' Create concatenated value list from array of values.
        ItemList = Join(Fields(), Separator)
    End If
    ConcatenateField = ItemList
End Function

Open in new window

Your query would look like:

   GenericItem, Posn, Item, ConcatenateField("tblGenericBOMMatrix","Option","GenericItem",[GenericItem]) AS Options
   GenericItem, Posn, Item;

Open in new window

if GenericItem can be considered the group key.
Distinguished Expert 2017

If you are not exporting all the columns and rows of a table, then create a query that  selects the data you want and formats and sorts it if necessary.  Save the querydef.  Then use the querydef rather than the table in the TransferText method.

ALSO, if you want an export spec, you must export the query ONCE manually.  Save the export spec (press the advanced button to get to the option).  Then reference the export spec in the TransferText method.

Intellisense is your friend.  When you type DoCmd.TransferText (space) - you will get instructions on how to complete the instruction.
Gary CroxfordOperations Support Analyst



Thank you for help - sorry I took a while to respond but have been away from the office,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial