Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Microsoft Access/ DeConcatenate Values in field separated by a semicolon

Posted on 2015-02-24
13
Medium Priority
?
325 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

604 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