?
Solved

Microsoft Access/ DeConcatenate Values in field separated by a semicolon

Posted on 2015-02-24
13
Medium Priority
?
301 Views
Last Modified: 2016-02-11
I have a Microsoft Access Table that has a field which contains multiple values separated by semicolons, see example below

Field 1      Field 2
a1              1a;2a;3a;4a;
a2              5a;6a;7a;8a:
a3              9a;10a;11a:12a;

I would like to have a table that has these values that looks like the following
Field 1       Field 2
a1               1a
a1               2a
a1               3a
a1               4a
a2               5a
a2               6a
a2               7a
a2               8a
a3               9a
a3               10a
a3               11a
a3               12a
0
Comment
Question by:rjordanbots
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40629727
any idea how many values might be in [Field 2]?

I have a function, fnSplit() which I use for cases like this.  That function, along with a table (tbl_Numbers) which contains a single field (lng_Number) and 10 records (the values 0-9) will allow you to get what you are looking for.  The function looks like:

Public Function fnSplit(SplitWhat As Variant, WhichVal As Long, _
                      Optional Delimiter As String = ";") As Variant

    Dim strArr() As String
    If IsNull(SplitWhat) Then
        fnSplit = Null
    ElseIf WhichVal < 0 Or WhichVal > 9 Then
        fnSplit = "Too Many Values"
    Else
        strArr = Split(SplitWhat, Delimiter)
        If WhichVal > UBound(strArr) Then
            fnSplit = Null
        Else
            fnSplit = strArr(WhichVal)
        End If
    End If

End Function

Open in new window

With this function saved in a public code module, your query would look something like:

SELECT [Field1], [Field2], fnSplit([Field2], lng_Number) as ParsedField
FROM yourTable, tbl_Numbers
WHERE [Field2] IS NOT NULL
AND fnSplit([Field2], lngNumber) IS NOT NULL

If you have more than 10 elements in Field2, then increase the number of records in tbl_Numbers and simply add one to the previous value.
0
 

Author Comment

by:rjordanbots
ID: 40629737
Which value would I specifiy for SplitWhat and which value would I specify for Whichval ?
0
 

Author Comment

by:rjordanbots
ID: 40629752
The number of values in field 2 varies. Ultimately, I need a new table that would look like this

Field 1       Field 2
a1               1a
a1               2a
a1               3a
a1               4a
a2               5a
a2               6a
a2               7a
a2               8a
a3               9a
a3               10a
a3               11a
a3               12a
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40629760
place this codes in a regular module

change "tablename" with actual name of table
Sub UpdateAppendRecords()
Dim rs As DAO.Recordset, j As Integer, varArr() As String
Set rs = CurrentDb.OpenRecordset("TableName")

Do Until rs.EOF
    If InStr(rs!Field2, ";") Then
        varArr = Split(rs!Field2, ";")
        rs.Edit
        rs!Field2 = varArr(0)
        rs.Update
        
        For j = 1 To UBound(varArr)
            rs.AddNew
			rs!Field1=rs!Field1
            rs!Field2 = varArr(j)
            
            ' add the other fields here
            ' rs!FieldName=rs!FieldName
            
             rs.Update
         Next
    End If
rs.MoveNext
Loop
rs.Close
End Sub

Open in new window

0
 

Author Comment

by:rjordanbots
ID: 40629842
I will try this code and follow up.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40629843
"Which value would I specifiy for SplitWhat and which value would I specify for Whichval ? " 

From the example data you provided, the SplitWhat value is the field that contains the multiple values (Field2).  The WhichVal number is the lng_Number value which comes from tbl_Numbers.  Try it by replacing Field1 and Field2 in the following query with the actual fields from your table.  Make sure you don't forget to create tbl_Numbers as indicated in my previous post.

SELECT [Field1], [Field2], lng_Number, fnSplit([Field2], lng_Number) as ParsedField
FROM yourTable, tbl_Numbers
WHERE [Field2] IS NOT NULL
AND fnSplit([Field2], lng_Number) IS NOT NULL

Open in new window

0
 
LVL 46

Expert Comment

by:aikimark
ID: 40629899
This could also be done with a pair of queries:
Query1
Insert Into tblTarget (Field1, Field2)
Select Field1, Left(Field2, Instr(Field2, ";")-1)
From tblSource
Where Len(tblSource.Field2)<>0

Open in new window

Query2
Update tblSource
Set Field2 = Mid(Field2, Instr(Field2, ";")+1)
Where Len(tblSource.Field2)<>0

Open in new window


If you create a macro, you can execute these sequentially until all the tblSource.Field2 values are zero length.
You can also create a short VBA routine to do these.
Public Sub Source2Target()
  Do
    dbEngine(0)(0).Execute "Query1"
    dbEngine(0)(0).Execute "Query2"
  Loop Until dbEngine(0)(0).RecordsAffected = 0
End Sub

Open in new window

0
 
LVL 46

Expert Comment

by:aikimark
ID: 40630370
Having a good night's sleep brought to mind the possibility that the syntax for stored queries would probably be this:
Public Sub Source2Target()
  Do
    dbEngine(0)(0).QueryDefs("Query1").Execute
    dbEngine(0)(0).QueryDefs("Query2").Execute
  Loop Until dbEngine(0)(0).RecordsAffected = 0
End Sub

Open in new window

The original syntax would work if you substituted the actual SQL for the names of the queries.
0
 

Author Comment

by:rjordanbots
ID: 40631129
The only portion of code that seems to partially work is
Sub UpdateAppendRecords()
Dim rs As DAO.Recordset, j As Integer, varArr() As String
Set rs = CurrentDb.OpenRecordset("TableName")

Do Until rs.EOF
    If InStr(rs!Field2, ";") Then
        varArr = Split(rs!Field2, ";")
        rs.Edit
        rs!Field2 = varArr(0)
        rs.Update
       
        For j = 1 To UBound(varArr)
            rs.AddNew
                  rs!Field1=rs!Field1
            rs!Field2 = varArr(j)
           
            ' add the other fields here
            ' rs!FieldName=rs!FieldName
           
             rs.Update
         Next
    End If
rs.MoveNext
Loop
rs.Close
End Sub
                           

Field 1 is not populating with any value.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40631168
Did you try the function and query mentioned in my first and second posts?

If you need to dump this data into a new table, save the code I provided, write the query, view the results, then change the query to a make-table query.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40631220
here is the revised code
Sub UpdateAppendRecords()
Dim rs As DAO.Recordset, j As Integer, varArr() As String, vField1
Set rs = CurrentDb.OpenRecordset("TableName")

Do Until rs.EOF
    If InStr(rs!Field2, ";") Then
        varArr = Split(rs!Field2, ";")
        rs.Edit
        rs!Field2 = varArr(0)
        rs.Update
       
        For j = 1 To UBound(varArr)
            
        If varArr(j) & "" <> "" Then
            vField1 = rs!field1
            rs.AddNew
            rs!field1 = vField1
            rs!Field2 = varArr(j)
           
            ' add the other fields here
            ' rs!FieldName=rs!FieldName
           
             rs.Update
        End If
         Next
    End If
rs.MoveNext
Loop
rs.Close
End Sub

Open in new window

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 40631252
better, use this codes
Sub UpdateAppendRecords()
Dim rs As DAO.Recordset, j As Integer, varArr() As String, rs1 As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("TableName")
Set rs1 = CurrentDb.OpenRecordset("TableName")

Do Until rs.EOF
    If InStr(rs!Field2, ";") Then
        varArr = Split(rs!Field2, ";")
        rs.Edit
        rs!Field2 = varArr(0)
        rs.Update
       
        For j = 1 To UBound(varArr)
            
        If varArr(j) & "" <> "" Then
            rs1.AddNew
            rs1!field1 = rs!field1
            rs1!Field2 = varArr(j)
           
            ' add the other fields here
            ' rs!FieldName=rs!FieldName
           
             rs1.Update
        End If
         Next
    End If
rs.MoveNext
Loop
rs.Close
rs1.Close
End Sub

Open in new window

0
 

Author Closing Comment

by:rjordanbots
ID: 40632146
The last code entry that was posted worked, thank you for your help.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question