Solved

Microsoft Access/ DeConcatenate Values in field separated by a semicolon

Posted on 2015-02-24
13
261 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 47

Expert Comment

by:Dale Fye (Access MVP)
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 47

Expert Comment

by:Dale Fye (Access MVP)
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 45

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 45

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 47

Expert Comment

by:Dale Fye (Access MVP)
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 500 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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