Dividing a table in 2 separate tables by parsing a string

Question

I have a table that roughly looks like this:

the 1300 and 850 stand for frequency on wich these fibre cables are checked

The real problem is that the 1300 and 850 aren't set values. In a different file they could be "100" and "320", so I can't just look for "850" or "1300" to seperate the entries.

There are a few things that I can be sure of:

• There's always 2 different frequencies (let's call them "A" and "B" from now on)
• There are always the same amount of "A" entries as there are "B" entries
• the string is always a variations of \<A>\<A>nm_<LocationName>_<CoreNumber>.SOR

What I would like to have is 2 seperate tables, 1 for all of the "A" entries and one for the "B" entries.

How can I do this?

It doesn't matter if I have to use SQL or VBA

---

Edit:

By looking around on the internet, I have gained a general idea of how I would like this to work:

• open the table as a recordset.
• search every line for the value between the \'s. Example: \<value>\
• for every new value between the \ \
• fill the first table with all entries that have the first value (in our example 1300)

I just have no clue how to translate this into code, the person that knows how to do this, easy points

Answer 1

Thanks to TKEyi60's answer, I was put on the right track. Had to tweak the code here and there to come to this solution:

Public Function SplitTable()

Dim SQL As String
Dim strMOD As String
Dim strFULL As String
Dim strNEW As String
Dim charPOS As Integer
Dim strLEN As Integer
Dim i As Long
Dim j As Long
Dim alrEXIST As Boolean
Dim strTABLES() As Variant
Dim Rcst As DAO.Recordset
Dim dbs As DAO.Database
Dim tdfloop As DAO.TableDef

i = 0

Set dbs = CurrentDb

For Each tdfloop In dbs.TableDefs
    ReDim Preserve strTABLES(0 To i)
    strTABLES(UBound(strTABLES)) = tdfloop.Name
    i = i + 1
Next tdfloop

Set dbs = Nothing

'Select all the rows in the table so they can be added to a Recordset

SQL = " SELECT * FROM tblTotaalVerlies"

Set Rcst = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)

Rcst.MoveFirst

Do Until Rcst.EOF = True
    strFULL = Rcst![FileName] 'set this to string so it can be worked with
    strLEN = Len(strFULL) 'gets the length of the filename
    strMOD = Right(strFULL, strLEN - 1) 'removes the first \
    charPOS = InStr(strMOD, "\") 'gets the positiong of the next \
    strNEW = Mid(strMOD, 1, charPOS - 1)

   'use this to check and see if the name is a table already
    For j = 0 To i - 1
        If strNEW = strTABLES(j) Then
            alrEXIST = True 'boolean created for if table exists
        End If
    Next j

    'if not a table, create a table
    If alrEXIST = False Then

        DoCmd.RunSQL "CREATE TABLE " & strNEW & " ([Filename] varchar(32), [Verlies] varchar(32))"

        'Renew tabledef array
        i = i + 1
        ReDim Preserve strTABLES(0 To i - 1)
        strTABLES(UBound(strTABLES)) = strNEW
    End If

    alrEXIST = False 'reset value to false
    Rcst.MoveNext 'Move to the next record before restarting the loop

Loop

Set Rcst = Nothing

End Function

Answer 2

So I may have made is sound way easier and bit off more than I could chew, but I was able to create something that works on my MS Access with a sample Database. I did all this from just quick Google-fu so it may not be as elegant as an expert. But it works. This only takes the existing table and creates new tables, but if you need help transferring data then I can tweak this.

Dim myR As Recordset
Dim strSQL As String
Dim strMOD As String
Dim strFULL As String
Dim strNEW As String
Dim charPOS As Integer
Dim strLEN As Integer
Dim strTABLES() As Variant
Dim dbs As DAO.Database
Dim tdfloop As DAO.TableDef
Dim i As Long
Dim j As Long
Dim strNAME As String
Dim alrEXIST As Boolean
i = 0
Set dbs = CurrentDb

With dbs
    For Each tdfloop In .TableDefs
        ReDim Preserve strTABLES(0 To i)
        strTABLES(UBound(strTABLES)) = tdfloop.Name
        i = i + 1
    Next tdfloop
End With

Set dbs = Nothing

'select all the rows in your table so we can add them to recordset

strSQL = "SELECT * FROM Files"

'create your recordset
Set myR = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'now to access each row we use a loop
'if you're not sure the field names, you can access them like this:
'myR.Fields(1).Value
'or if you do know the field name then this
'myR![Filename]

myR.MoveFirst 'This just makes sure you're starting from the first record

Do Until myR.EOF = True

    strFULL = myR![FileName] 'set this to string so it can be worked with
    strLEN = Len(strFULL) 'gets the length of the string aka filename
    strMOD = Right(strFULL, strLEN - 1) 'removes the first \
    charPOS = InStr(strMOD, "\") 'gets the positiong of the next \
    strNEW = Mid(strMOD, 1, charPOS - 1) 'gets the substring from left to \

    'use this to check and see if the name is a table already
    For j = 0 To i - 1
        If strNEW = strTABLES(j) Then
            alrEXIST = True 'boolean created for if table exists
        End If
    Next

    'if not a table, create a table
    If alrEXIST = False Then
        DoCmd.RunSQL "CREATE TABLE " & strNEW & " ([Field1] text(255), [Field2] text(255))"
    End If

    alrEXIST = False 'reset value to false
    myR.MoveNext 'Move to the next record before restarting the loop


Loop

Set myR = Nothing