Solved

Microsoft Access/ DeConcatenate Values in field separated by a semicolon

Posted on 2015-02-24
13
224 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
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
Which value would I specifiy for SplitWhat and which value would I specify for Whichval ?
0
 

Author Comment

by:rjordanbots
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
I will try this code and follow up.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
"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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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
Comment Utility
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
Comment Utility
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)
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
The last code entry that was posted worked, thank you for your help.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

771 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now