split delimited field into different rows 3

[maggsz]

simplified here is what I have:
ID colors
1 white, black, red
2 red, orange
3 blue

Here is what I want to achieve:
ID colors
1 white
1 black
1 red
2 red
2 orange
3 blue

I have an Access 2007 table with some IDs having more than one color. There is no limit to the amount of colors that an ID could have.
This query is part of the process of migrating one data structure to another.

thanks in advance
maggsz

 

[maggsz]

Is the split function the way to go?

maggsz

 

[MajP]

Off the top of my head (untested) and using vba something like

dim strColors() as string
dim ID as integer
dim rs as dao.recordset
dim i as integer
dim strSql as string
set rs = currentdb.openrecordset("yourTableName")
do while not rs.eof
  ID = rs!ID
  strColors = split(rs!colors, ",")
  for i = lbound(colors) to ubound(colors)
    strSql = "Insert into yourNewTable (ID, Colors) values (" & ID & ", '" & colors(i) & "')"
    currentdb.execute strSql 
  next i
  rs.moveNext
loop

 

[maggsz]

Hi MajP
thanks for your reply.
I will try this out now and let you know how I go

Maggz

 

[dhookom]

Another solution is to write a wrapper for the Split function so you can use it in a query.

Public Function MySplit(varText As Variant, _
        intSection As Integer, Optional strDelim As String = ",") As Variant
    On Error GoTo errHandler
    MySplit = Split(varText, strDelim)(intSection - 1)
    Exit Function
errHandler:
    MySplit = Null
    Exit Function
End Function

Then create a table [tblNums] with a single numeric field [num] and enter values 1 through you maximum number of colors. You can then create a query with SQL of:

SELECT ID, MySplit([colors],[Num],",") AS Color
FROM tt_Maggsz, tblNums
WHERE MySplit([colors],[Num],",") Is Not Null
ORDER BY ID;

Duane
Hook'D on Access
MS Access MVP

 

[maggsz]

Hi dhookom
thanks for your reply. This looks an interesting variation.
Looking at the code where does "intSection" come from

Maggz

 

[dhookom]

intSection is just the section number like 1st, 2nd, or 3rd color from the value.

Duane
Hook'D on Access
MS Access MVP

 

[maggsz]

Hi dhookom
With the intSection I am not sure how to get this into the function.

Maggsz

 

[maggsz]

Hi MajP
I am getting an error with:
for i = lbound(colors) to ubound(colors)
there is a type mismatch.
I am not sure why. colours is a text field which from my understanding gets split into the strQualifications array which is a string.
What am I missing?

Maggsz

 

[Golom]

If you want a pure SQL approach look at faq701-6293. It's a bit convoluted but it is designed to do that.

 

[maggsz]

Hi Golom
thanks for your reply.
Yes it works a treat. However as I have spaces after the comma these are included in the results of the last query.
Is there a way to remove the space? Trim maybe? And if so how could I include this so that it would not affect a color with spaces in between such as "dark red".

Maggsz

 

[Golom]

Yes you can use Trim$ As in

Trim$(Mid("," & E.Children & ",",
S1.N + 1 ,
S2.N - S1.N - 1)) As SomeAlias.

Trim$ removed leading and trailing spaces but embedded spaces are not affected.

 

[maggsz]

Hi Golom
I still get the leading space. I am not sure if I put the trim$ in the right place.

SELECT EmployeeID, Mid("," & E.Children & ",",
S1.N + 1 ,
S2.N - S1.N - 1) AS Child
FROM tblEmployees AS E, qryInt2 AS S1, qryInt2 AS S2
WHERE Mid$("," & E.Children & "," ,S1.N , 1 ) = ','
and Mid$("," & E.Children & "," ,S2.N , 1 ) = ','
and S1.N > 0 AND S2.N > 0
and S2.N <= Len(E.Children) + 2
and S1.N + 1 < S2.N
and InStr(1,Trim$(Mid("," & E.Children & ",",
S1.N + 1 ,
S2.N - S1.N - 1)),",")=0
ORDER BY 1, 2;

I really do appreciate the time you have taken to help me with this example
maggsz

 

[maggsz]

Hi Golom
sorry I did put the trim in the wrong place. It was not till I saw my reply after I submitted it that I noticed where the trim should have been, near the top as in:

SELECT EmployeeID, Trim$(Mid("," & E.Children & ",",
S1.N + 1 ,
S2.N - S1.N - 1)) AS Child
FROM tblEmployees AS E, qryInt2 AS S1, qryInt2 AS S2
WHERE Mid$("," & E.Children & "," ,S1.N , 1 ) = ','
and Mid$("," & E.Children & "," ,S2.N , 1 ) = ','
and S1.N > 0 AND S2.N > 0
and S2.N <= Len(E.Children) + 2
and S1.N + 1 < S2.N
and InStr(1,Mid("," & E.Children & ",",
S1.N + 1 ,
S2.N - S1.N - 1),",")=0
ORDER BY 1, 2;

Golom I really have learnt a lot from this example. So once again many thanks for sharing your knowledge.

maggsz

 

[MajP]

The mistake was a typo. The variable name is strColors not colors
For i = LBound(strColors) To UBound(strColors)
strSql = "Insert into tblColors (ID, Colors) values (" & ID & ", '" & strColors(i) & "')"

Also to check for nulls
strColors = Split(Nz(rs!colors, ""), ",")

 

[maggsz]

thank you Golom, dhookom and MajP. I am still learning and developing my vba skills so your help keeps me developing and finetuning my vba.

maggsz

 

[maggsz]

Hi MajP
Yes your corrections worked perfect.
Before I had read your reply I had changed color to strColor but still no luck. Once I read your reply and included the null code it worked. For me it has highlighted the importance of capturing nulls.
Thanks

maggsz

 

[dhookom]

maggsz,
As a follow-up the intSize is an argument sent in to the MySplit() function. Maybe you found the line continuation " _" a bit confusing as there are three arguments with the third being optional. The actual value for intSection comes from the Num field in tblNums.

Duane
Hook'D on Access
MS Access MVP

 

[maggsz]

Hi dhookom
I will try your suggestion when I am at work tomorrow
thanks

maggsz

 

[maggsz]

Hi dhookom
I tried your suggestion and yes it works great.
thanks

maggsz