Separating data and cleaning Access Table

Question

I have a table that has the columns below. I'm trying to separate the Additional Fig and Additional Item in new records but have old ID still associated with them.

So that I end up with a table like the one below. On ID 89 there are two additional fig but only one additional item, on records like these the missing additional item will be left blank or 9999 needs to put into the record.

The code below is from Split Field Into Multiple Records in Access DB. it gets me close to what I want to do. The code splits one column into many but I need to do two into many and find a way to deal with record where there is a additional fig but no additional item or vice versa.

Option Explicit

    Public Sub ReformatTable()

        Dim db          As DAO.Database
        Dim rs          As DAO.Recordset
        Dim rsADD       As DAO.Recordset

        Dim strSQL      As String
        Dim strField1   As String
        Dim strField2   As String
        Dim varData     As Variant
        Dim strAppCode  As String
        Dim i           As Integer

        Set db = CurrentDb

        ' Select all eligible fields (have a comma) and unprocessed (Field2 is Null)
        strSQL = "SELECT AppCode, Field1, Field2 FROM Table1 WHERE ([Field1] Like ""*,*"") AND ([Field2] Is Null)"

        ' This recordset is only used to Append New Records
        Set rsADD = db.OpenRecordset("Table1", dbOpenDynaset, dbAppendOnly)

        Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
        With rs
            While Not .EOF

                ' Do we need this for newly appended records?
                strAppCode = !AppCode

                strField1 = !Field1
                varData = Split(strField1, ",") ' Get all comma delimited fields

                ' Update First Field
                .Edit
                !Field2 = Trim(varData(0)) ' remove spaces before writing new fields
                .Update

                ' Add new fields for remaining data at end of string
                For i = 1 To UBound(varData)
                    With rsADD

                        .AddNew

                        ' ***If you need a NEW Primary Key based on current AppCode
                        !AppCode = strAppCode & "-" & i

                        ' ***If you remove the Unique/PrimaryKey and just want the same code copied
                        !AppCode = strAppCode

                        ' Copy previous Field 1
                        !Field1 = strField1

                        ' Insert Field 2 based on extracted data from Field 1
                        !Field2 = Trim(varData(i)) ' remove spaces before writing new fields
                        .Update
                    End With
                Next
                .MoveNext
            Wend

            .Close
            rsADD.Close

        End With

        Set rsADD = Nothing
        Set rs = Nothing
        db.Close
        Set db = Nothing

    End Sub

you want [split](https://support.office.com/en-us/article/split-function-876f246a-5144-44fa-889f-10078c7878f5). — Warcupine, Mar 02 '20 at 19:20
Does this answer your question? [How to Split a String and Store in MS Access table](https://stackoverflow.com/questions/34443316/how-to-split-a-string-and-store-in-ms-access-table) — June7, Mar 02 '20 at 19:37

June7 · Accepted Answer · 2020-03-03T00:56:32.097

Build a second table with fields:
ID (autonumber)
FID (long integer)
Fig (text)
Item (text)

Consider:

Dim db          As DAO.Database
Dim rsOld       As DAO.Recordset
Dim rsNew       As DAO.Recordset
Dim strF1       As String
Dim strF2       As String
Dim varF        As Variant
Dim varI        As Variant
Dim strID       As String
Dim x           As Integer
Dim intLoop     As Integer
Set db = CurrentDb
Set rsOld = db.OpenRecordset("Table1")
db.Execute "DELETE FROM Table2"
Set rsNew = db.OpenRecordset("Table2")
With rsOld
    Do While Not .EOF
        strID = !ID
        strF1 = Replace(Nz(!AdditionalFig, ""), ", ", ",")
        If Right(strF1, 1) = "," Then strF1 = Left(strF1, Len(strF1) - 1)
        varF = Split(strF1, ",")
        strF2 = Replace(Nz(!AdditionalItem, ""), ", ", ",")
        If Right(strF2, 1) = "," Then strF2 = Left(strF2, Len(strF2) - 1)
        varI = Split(strF2, ",")
        intLoop = IIf(UBound(varF) > UBound(varI), UBound(varF), UBound(varI)) + 1
        For x = 1 To intLoop
            With rsNew
                .AddNew
                !FID = strID
                If UBound(varF) >= x - 1 Then !Fig = varF(x - 1)
                If UBound(varI) >= x - 1 Then !Item = varI(x - 1)
                .Update
            End With
        Next
        .MoveNext
    Loop
End With

Separating data and cleaning Access Table

1 Answers1