Split string separated by commas in access

Question

I have a table tblPartnerships structured as
ID      Partnerships
1     Finance, IT, Operations
2     Legal, Compliance, IT, HR

I need to extract all of the comma separated keywords from Partnerships column and put it in another mastertable with each word as a separate row so that it shows up as

Finance
IT
Operations
Legal
Compliance
IT
HR

I know there is a split function such as varList = Split(stTmp, ",") but i am completely lost how to iterate through the entire column tblPartnerships.Partnerships insert it into MasterTable.Rowheading
Any Access query or vba code will be highly appreciated

Use VBA to manipulate a recordset object and in looping structure read each record of the recordset. — June7, Feb 21 '18 at 03:02
is this a "one-time thing", to make a reference table? (If yes, I would suggest creating your list manually in Excel.) Or would this be run regularly? (If so, what is it accomplishing, and surely the output table needs more than one field?) — ashleedawg, Feb 21 '18 at 04:01

score 2 · Answer 1 · answered Feb 21 '18 at 07:22

Create an Append query with a single parameter, the word to be imported. The query's source SQL should be something like this:

PARAMETERS [prmWord] Text (50);
INSERT INTO T ( Words )
SELECT [prmWord] AS _Word;

Where T is the name of your Table (master) and Words is the name of the Field.

Then just loop through the Partnerships recordset, split the value and import each word using the above query.

Sub SplitAndImport()
    On Error GoTo ErrorTrap

    Dim rs As DAO.Recordset
    Set rs = CurrentDb().OpenRecordset("SELECT Partnerships FROM tblPartnerships;", dbOpenSnapshot)
    With rs
        If .EOF Then GoTo Leave
        .MoveLast
        .MoveFirst
    End With

    Dim arr As Variant
    Dim i As Long, ii As Long
    For i = 1 To rs.RecordCount
        arr = Split(rs![Partnerships], ",")
        For ii = LBound(arr) To UBound(arr)
            With CurrentDb().QueryDefs("QueryName")
                .Parameters("[prmWord]").Value = Trim(arr(ii))
                .Execute dbFailOnError
            End With
        Next
        rs.MoveNext
    Next

Leave:
    If Not rs Is Nothing Then rs.Close
    On Error GoTo 0
    Exit Sub

ErrorTrap:
    MsgBox Err.Description, vbCritical
    Resume Leave
End Sub

score 0 · Answer 2 · answered Mar 13 '21 at 18:19

This may not work as accurately with your current database design. You may try using multivalued fields. Later, if you want view multi values each on single row then use "AssignedTo" command.

SELECT [title], [lcs].[AssignedTo].[value] AS Expr1
FROM Product;

Where lcs is a multivalued field and title is a text field.

Split string separated by commas in access

2 Answers2