Combine values from many table rows into one string value

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
Gary CroxfordOperations Support AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

A.E. VeltstraCommented:
Are you attempting to export the table contents into a CSV file? Access provides both manual and programmatic tools for that:
https://stackoverflow.com/questions/34603615/export-specific-columns-from-access-table-to-csv-using-select-into#34620228
Dale FyeOwner, Developing Solutions LLCCommented:
Take a look at the article posted here

This is designed to do exactly what you are looking for.
Gustav BrockCIOCommented:
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:
'   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

Open in new window

Your query would look like:

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

Open in new window

if GenericItem can be considered the group key.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
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 AnalystAuthor Commented:
Gentlemen,

Thank you for help - sorry I took a while to respond but have been away from the office,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.