Separating comma separated values (1 Viewer)

vik01 · Jun 29, 2011

Hi

Firstly I would like to say I am new to Access, so I don't know all the terminology.

Can't describe exactly what I'm trying to achieve here, so, in a nutshell, I want know if there is any way I can automate the creation Table 2 from Table 1:

Table
1
Customer Product
1 A,C
2 A,B,C,D
3 A,D

Table
2
Customer Product
1 A
1 C
2 A
2 B
2 C
2 D
3 A
3 D

Thanks in advance for your help.

Regards
Vik

Beetle · Jun 29, 2011

Here is some example code to do what you want. It could be run from the command button of a form, etc. The code loops through Table1, splitting the Product field into an array (using the comma as the delimiter) and inserting each element of the array into Table2, along with the Customer value.

Code:

Dim MyArray As Variant
Dim rs As DAO.Recordset
Dim i As Integer
Dim strSQL As String
 
Set rs = CurrentDb.OpenRecordset("[COLOR=red]Table1[/COLOR]")
 
With rs
    .MoveFirst
    Do While Not .EOF
        MyArray = Split(![COLOR=red]Product[/COLOR], ",")
            For i = 0 To UBound(MyArray)
            strSQL = "Insert Into [COLOR=red]Table2[/COLOR] ([COLOR=red]Customer[/COLOR], [COLOR=red]Product[/COLOR]) Values(" _
                   & ![COLOR=red]Customer[/COLOR] & ", """ & MyArray(i) & """);"
            CurrentDb.Execute strSQL
        Next i
        .MoveNext
    Loop
End With

The items in red text would need to modified to reflect your actual table and field names. Also, I have assumed that your Customer field is numeric. If it is text, you will need to add string delimiters in the SQL as follows;

Code:

strSQL = "Insert Into Table2 (Customer, Product) Values[COLOR=black]("""[/COLOR] _
                   & !Customer & """, """ & MyArray(i) & """);"

vik01 · Jun 30, 2011

Beetle said:
Here is some example code to do what you want. It could be run from the command button of a form, etc. The code loops through Table1, splitting the Product field into an array (using the comma as the delimiter) and inserting each element of the array into Table2, along with the Customer value.

Code:

Dim MyArray As Variant Dim rs As DAO.Recordset Dim i As Integer Dim strSQL As String Set rs = CurrentDb.OpenRecordset("[COLOR=red]Table1[/COLOR]") With rs .MoveFirst Do While Not .EOF MyArray = Split(![COLOR=red]Product[/COLOR], ",") For i = 0 To UBound(MyArray) strSQL = "Insert Into [COLOR=red]Table2[/COLOR] ([COLOR=red]Customer[/COLOR], [COLOR=red]Product[/COLOR]) Values(" _ & ![COLOR=red]Customer[/COLOR] & ", """ & MyArray(i) & """);" CurrentDb.Execute strSQL Next i .MoveNext Loop End With

The items in red text would need to modified to reflect your actual table and field names. Also, I have assumed that your Customer field is numeric. If it is text, you will need to add string delimiters in the SQL as follows;

Code:

strSQL = "Insert Into Table2 (Customer, Product) Values[COLOR=black]("""[/COLOR] _ & !Customer & """, """ & MyArray(i) & """);"

Hi Beetle

Thanks for your reply.

I have tried what you have suggested, but I am getting the following error message:
_________________________________________

Run-time error '3134'
Syntax error in INSERT INTO statement.
_________________________________________

When I click 'debug' it is highlighting 'CurrentDb.Execute strSQL'

Just for further clarification I did the following:

1) In the create tab I clicked on Form Design
2) I created a button
3) right clicked on the button then 'build event'
4) clicked on code builder
5) pasted in your code (replacing the red text with the appropriate table and field names)
6) Open the form and clicked the button

Can you help me please?

Thanks

Vik

Beetle · Jun 30, 2011

Can you repost the code exactly as you have it now, and tell what the data types are of the relevant feilds in the table?

vik01 · Jul 8, 2011

Hi it works now. Thanks! I do have another problem that I can't solve:

This is what my data looks like:

Table 1
TaskID Requirement Duration
1 R1,R2 6
2 R3,R5 4
3 R1,R3,R6 8
4 R2,R4 2
5 R5 4
6 R1,R6 10
7 R3,R7,R8 8

What I am trying to do is create a new table that shows how many hours were spent on each requirement.

The end table should look like this:

Table 2
ID Requirement Duration
1 R1 10.7
2 R2 4

I know that table 1 doesn't indicate how the duration is split, which is why I am trying to find out the average instead.

Could you help me with this please?

Thanks
Vik

Beetle · Jul 8, 2011

So, given a table (Table1) with the following data;

TaskID--Requirement--Duration
1--------R1,R2--------6
2--------R3,R5--------4
3--------R1,R3,R6-----8
4--------R2,R4--------2
5--------R5-----------4
6--------R1,R6--------10
7--------R3,R7,R8-----8

And another table (Table2) with the following fields;

ID (Autonumber)
Requirement
Duration

The following code

Code:

Dim MyArray As Variant
Dim rs As DAO.Recordset
Dim i As Integer, x As Integer
Dim sngDuration As Single
Dim strSQL As String
 
Set rs = CurrentDb.OpenRecordset("Table1")
 
With rs
    .MoveFirst
    Do While Not .EOF
        MyArray = Split(!Requirement, ",")
        x = UBound(MyArray)
        For i = 0 To x
            'Divide the duration by the number of elements in the Array
            'Array is zero based so we need to add 1 to x before dividing
            sngDuration = Format(!Duration / (x + 1), "0.0")
            strSQL = "Insert Into Table2 (Requirement, Duration)" _
                   & " Values(""" & MyArray(i) & """, " & sngDuration & ");"
            CurrentDb.Execute strSQL
        Next i
        .MoveNext
    Loop
End With

Will result in the following record set in Table2;

ID--Requirement--Duration
1-----R1----------3
2-----R2----------3
3-----R3----------2
4-----R5----------2
5-----R1----------2.7
6-----R3----------2.7
7-----R6----------2.7
8-----R2----------1
9-----R4----------1
10----R5----------4
11----R1----------5
12----R6----------5
13----R3----------2.7
14----R7----------2.7
15----R8----------2.7

Then, creating a Totals query of Table2 like the following;

SELECT Table2.Requirement, Sum(Format([Duration],"0.0")) AS TotalDuration
FROM Table2
GROUP BY Table2.Requirement;

Returns the following results;

Requirement-----TotalDuration
R1---------------10.7
R2---------------4
R3---------------7.4
R4---------------1
R5---------------6
R6---------------7.7
R7---------------2.7
R8---------------2.7

vik01 · Jul 11, 2011

Thanks Beetle - this is perfect! It does exactly what I asked.

Just one more thing:

I want to add additional columns to Table 1:

-Task text (memo) - so requirement R1 can have more than 1 'Task Text'
-Document ID (secondary key) - one to many relationship with Task ID

In summary, I want to know how to insert additional fields from table 1 to table 2.

Thanks very much for your help.

Vik

Beetle · Jul 11, 2011

I'm not sure I follow what you want here.

I want to add additional columns to Table 1:

-Task text (memo) - so requirement R1 can have more than 1 'Task Text'

Since Table1 has multiple values in the Requirement field (like R1,R2, etc.), if you add a memo field, how are you going to determine if the value in the memo field is related to R1 or R2 or whatever? Is there also some type of delimiter in this data?

-Document ID (secondary key) - one to many relationship with Task ID

Where is this supposed to go? In a third table related one-to-many to Table2?

vik01 · Jul 14, 2011

Beetle said:
I'm not sure I follow what you want here.

Since Table1 has multiple values in the Requirement field (like R1,R2, etc.), if you add a memo field, how are you going to determine if the value in the memo field is related to R1 or R2 or whatever? Is there also some type of delimiter in this data?

Where is this supposed to go? In a third table related one-to-many to Table2?

I see what you mean. Is it possible to turn table 1 into table 2:

Table 1

Table 1
TaskID Requirement Memo Duration DocumentID
1 R1,R2 Memo1 6 1
2 R3,R5 Memo2 4 1
3 R1,R3,R6 Memo3 8 1
4 R2,R4 Memo4 2 1
5 R5 Memo5 4 2
6 R1,R6 Memo6 10 2
7 R3,R7,R8 Memo7 8 3

Table 2

ID Requirement Memo Duration DocumentID
1 R1 Memo1 3 1
2 R1 Memo3 2.7 1
3 R1 Memo6 5 2
4 R2 Memo1 3 1
5 R2 Memo4 1 1
6 R3 Memo2 2 1
7 R3 Memo7 2.7 3

If possible, what I want is to be able to do is query table 2 to find out the total duration of each requirement, estimate, or memo...

Thanks for your help
Vik

Beetle · Jul 14, 2011

Just add the additional fields to the Insert statement in the code;

Code:

Dim MyArray As Variant
Dim rs As DAO.Recordset
Dim i As Integer, x As Integer
Dim sngDuration As Single
Dim strSQL As String
 
Set rs = CurrentDb.OpenRecordset("Table1")
 
With rs
    .MoveFirst
    Do While Not .EOF
        MyArray = Split(!Requirement, ",")
        x = UBound(MyArray)
        For i = 0 To x
            'Divide the duration by the number of elements in the Array
            'Array is zero based so we need to add 1 to x before dividing
            sngDuration = Format(!Duration / (x + 1), "0.0")
            strSQL = "Insert Into Table2 (Requirement,MemoField,Duration,DocumentID)" _
                   & " Values(""" & MyArray(i) & """, """ & !MemoField _
                   & """, " & sngDuration & ", " & !DocumentID & ");"
            CurrentDb.Execute strSQL
        Next i
        .MoveNext
    Loop
End With

vik01 · Jul 19, 2011

Beetle said:

Just add the additional fields to the Insert statement in the code;

Code:

Dim MyArray As Variant
Dim rs As DAO.Recordset
Dim i As Integer, x As Integer
Dim sngDuration As Single
Dim strSQL As String
 
Set rs = CurrentDb.OpenRecordset("Table1")
 
With rs
    .MoveFirst
    Do While Not .EOF
        MyArray = Split(!Requirement, ",")
        x = UBound(MyArray)
        For i = 0 To x
            'Divide the duration by the number of elements in the Array
            'Array is zero based so we need to add 1 to x before dividing
            sngDuration = Format(!Duration / (x + 1), "0.0")
            strSQL = "Insert Into Table2 (Requirement,MemoField,Duration,DocumentID)" _
                   & " Values(""" & MyArray(i) & """, """ & !MemoField _
                   & """, " & sngDuration & ", " & !DocumentID & ");"
            CurrentDb.Execute strSQL
        Next i
        .MoveNext
    Loop
End With

This is great - thank you so much.

One final question - In the memo field there is some text that's in speech mark. When I run the code on this I get an error. Any way around this?

Thanks

Beetle · Jul 20, 2011

If by speech mark you mean a double quote, then try replacing the Insert statement part of the code with this;

strSQL = "Insert Into Table2 (Requirement,MemoField,Duration,DocumentID)" _
& " Values(""" & MyArray(i) & """, """ & Replace(!MemoField, """", """""") _
& """, " & sngDuration & ", " & !DocumentID & ");"

vik01 · Jul 21, 2011

Beetle said:
If by speech mark you mean a double quote, then try replacing the Insert statement part of the code with this;

strSQL = "Insert Into Table2 (Requirement,MemoField,Duration,DocumentID)" _
& " Values(""" & MyArray(i) & """, """ & Replace(!MemoField, """", """""") _
& """, " & sngDuration & ", " & !DocumentID & ");"

Thanks for this.

jbenner · Feb 3, 2017

Hi,
I had a similar situation where I had to use this, and it works great. I opted to run this based on a query and not a table, due to an import that I run before hand. I also set this in a module as I have to run several of them as part of the importing process. All was well until I ran into an instance where there were no values listed in the field to split. Basically I am missing code to tell the function not to run if the query is null or empty. .EOF is fine if you have something to start with but in this case I didn't and it shut down my entire import procedure. Any help would be really appreciated. Cheers!... Module Code posted below:

Function fSplitBenefits()

Dim MyArray As Variant
Dim rs As DAO.Recordset
Dim i As Integer
Dim strSQL As String

Set rs = CurrentDb.OpenRecordset("qryFoXBenefits")

With rs
.MoveFirst
Do While Not .EOF Or IsNotNull
MyArray = Split(!Benefits, ", ")
For i = 0 To UBound(MyArray)
strSQL = "Insert Into tblSurBenefits (FoID, Benefits) Values(" _
& !FoID & ", """ & MyArray(i) & """);"
CurrentDb.Execute strSQL
Next i
.MoveNext
Loop
End With

End Function

Beetle · Feb 11, 2017

This post is a few days old, so you may have already resolved the issue, but if not just check the Benefits field for null before you create the array;

Code:

With rs
.MoveFirst
Do While Not .EOF
If Not IsNull(!Benefits) Then
   MyArray = Split(!Benefits, ", ")
   For i = 0 To UBound(MyArray)
   strSQL = "Insert Into tblSurBenefits (FoID, Benefits) Values(" _
   & !FoID & ", """ & MyArray(i) & """);"
   CurrentDb.Execute strSQL
   Next i
End If
.MoveNext
Loop
End With

MarkK · Feb 11, 2017

Nice code beetle. I thought maybe you would find some of these alternatives interesting...

Code:

    Const SQL_INSERT As String = _
        "INSERT INTO Table2 ( Customer, Product ) " & _
        "VALUES ( p0, p1 )"

    Dim qdf As DAO.QueryDef
    Dim var
    
    Set qdf = CurrentDb.CreateQueryDef("", SQL_INSERT)

    With CurrentDb.OpenRecordset("Table1")
        Do While Not .EOF
            For Each var In Split(!Product, ",")
                qdf.Parameters(0) = !Customer
                qdf.Parameters(1) = var
                qdf.Execute dbFailOnError
            Next
            .MoveNext
        Loop
        .Close
    End With
    qdf.Close

Cheers,

Beetle · Feb 11, 2017

Thanks for chiming in MarkK. I am always interested in alternative views on ways to solve problems with code. I have used querydefs a lot, but not in the way you have here, so that's another method I can keep in mind for future reference. I appreciate the input.

jbenner · Apr 4, 2017

Hi,
Sorry this took me so long to circle back to. Thank you for your response. I tried inserting the 2 additional lines of code, and it keeps getting hung up on the .MoveFirst command. I suspect that because the query results are completely null (not just a particular record) it can't execute on the .MoveFirst as there's nothing to move to. I tried re-positioning your Null code outside of the With section and just after but neither option seemed to work. Still stumped.

Thanks,
Jennifer:banghead:

Separating comma separated values (1 Viewer)

vik01

Registered User.

Beetle

Duly Registered Boozer

vik01

Registered User.

Beetle

Duly Registered Boozer

vik01

Registered User.

Beetle

Duly Registered Boozer

vik01

Registered User.

Beetle

Duly Registered Boozer

vik01

Registered User.

Beetle

Duly Registered Boozer

vik01

Registered User.

Beetle

Duly Registered Boozer

vik01

Registered User.

jbenner

Registered User.

Beetle

Duly Registered Boozer

MarkK

bit cruncher

Beetle

Duly Registered Boozer

jbenner

Registered User.

Similar threads

Users who are viewing this thread