Solved

Microsoft Access/ DeConcatenate Values in field separated by a semicolon

Posted on 2015-02-24
13
280 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 (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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the 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 (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 48

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

728 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