The following was created to help a user in a forum.

The issue was a denormalized set of data that he wanted to cleanup, but not manually as there were far too many records to process.

Thake a simple example, a table

TblClients
ClientID
FirstName
LastName
OrderNumbers
…

with data like

1 | Joe | Doyle | 112,267,298,3009,1200
2 | Jane | Smith | 23,445,446,449
3 | Lois | Carleton | 1111
…

Now proper normalization dictates that we have an orders table to in which each order is entered instead of a single text field (OrderNumbers).

So we create another table

TblOrder
OrderID
ClientID
OrderNumber
…

so the task becomes how do I take the existing data and split each OrderNumber entry and append it to the TblOder table when I have 300K+ records?

Well, that’s where my procedure become useful.

'---------------------------------------------------------------------------------------
' Procedure : SplitFieldIntoChildTable
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Take a list from one parent table and split it up and insert them into a
'             child table related by the PK/FK
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
'sInputTable        Name of the Parent table containing the string to be split
'sDataFieldName     Name of the field in the parent table containing the string to be split
'sDelim             Delimiting character(s) that will be used to split the string
'sPKField           Name of the Primary Key field in the parent table
'sChildTable        Name of the Child table to which the split string values are to be inserted
'sChildFieldName    Name of the field in the child table to insert the values into
'sFKField           Name of the Foreign Key field in the child table
'
' Usage:
' ~~~~~~
'
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2016-01-10              Initial Release
'---------------------------------------------------------------------------------------
Function SplitFieldIntoChildTable(sInputTable As String, sDataFieldName As String, sDelim As String, sPKField As String, _
                                  sChildTable As String, sChildFieldName As String, sFKField As String)
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim sSQL                  As String
    Dim lPK                   As Long
    Dim sFieldValue           As String
    Dim aFieldValues          As Variant
    Dim i                     As Long

    Set db = CurrentDb

    sSQL = "SELECT [" & sPKField & "], [" & sDataFieldName & "] FROM [" & sInputTable & "];"
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
    With rs
        If .RecordCount <> 0 Then
            Do While Not .EOF
                lPK = rs(sPKField)
                sFieldValue = rs(sDataFieldName)
                aFieldValues = Split(sFieldValue, sDelim)
                For i = 0 To UBound(aFieldValues)
                    db.Execute "INSERT INTO [" & sChildTable & "] ([" & sFKField & "], [" & sChildFieldName & "])" & _
                               " VALUES (" & lPK & ", " & aFieldValues(i) & ");", dbFailOnError
'The following would be for textual data types
'                    db.Execute "INSERT INTO [" & sChildTable & "] ([" & sFKField & "], [" & sChildFieldName & "])" & _
'                               " VALUES (" & lPK & ", " & aFieldValues(i) & ");", dbFailOnError
                Next i
                .MoveNext
            Loop
        End If
    End With
    rs.Close

    Set rs = Nothing
    Set db = Nothing
End Function

With a single line of code I can quickly process all of the entries and be done with this aspect of the data normalization.

SplitFieldIntoChildTable "TblClients", "OrderNumbers", ",", "ClientID", "TblOrder", "OrderNumber", "ClientID"