Function to create Individual Records From Delimited String 1

[Flo79]

Hi There,

I am looking for a function in vba that would do the following:

I have a Table (A) with records that look like this:

Record 1 Column 1 = 23 Column 2 = a,b,c,d


I need a function to create another table (B) where records in table (A) are transformed to look like this:

Record 1 Column 1 = 23 Column 2 = a
Record 2 Column 1 = 23 Column 2 = b
Record 3 Column 1 = 23 Column 2 = c
Record 4 Column 1 = 23 Column 2 = d

Any sample code would be greatly appreciated.

Cheers!

FGP

 

[PHV]

And what have you tried so far ?
If ac2k or above you may consider the Split function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Flo79]

PHV,

You helped me greatly by mentioning "Split Function." It was the search term that I should have been using.
I found a solution to my problem here:

http://www.mvps.org/access/strings/str0003.htm

The original code is I think by Dev Ashish, I modified the 2 functions needed slightly to give me the ability to specify the string delimiter more easily.

-----------------------------------------------------------

Public Function GetCSWord(ByVal s, Indx As Integer, Optional strdelimiter)
'Returns the nth word in a specific field.
Dim WC As Integer, Count As Integer
Dim SPos As Integer, EPos As Integer
If IsMissing(strdelimiter) Then strdelimiter = " "
WC = CountCSWords(s, strdelimiter)
If Indx < 1 Or Indx > WC Then
GetCSWord = Null
Exit Function
End If
Count = 1
SPos = 1
For Count = 2 To Indx
SPos = InStr(SPos, s, strdelimiter) + 1
Next Count
EPos = InStr(SPos, s, strdelimiter) - 1
If EPos <= 0 Then EPos = Len(s)
GetCSWord = Trim(Mid(s, SPos, EPos - SPos + 1))
End Function

-----------------------------------------------------------
Function CountCSWords(ByVal s, strdelimiter) As Integer

'Counts the words in a string that are separated by commas.
Dim WC As Integer, Pos As Integer
If VarType(s) <> 8 Or Len(s) = 0 Then
CountCSWords = 0
Exit Function
End If
WC = 1
Pos = InStr(s, strdelimiter)
Do While Pos > 0
WC = WC + 1
Pos = InStr(Pos + 1, s, strdelimiter)
Loop
CountCSWords = WC
End Function
-----------------------------------------------------------
For those less familiar with custom function here is how to make it work:

Paste the above code into a New MS Access Module

Now,

Assuming that you have Table (A), which contains the following:

Record 1 Column 1 = 23 Column 2 = a,b,c,d


I can get the first record in the series by typing the following function in a query:

----------------------------------------
GetCSWord([Name of Column 1],1, ",")
----------------------------------------
The result of the Above Function will be "a"

----------------------------------------
GetCSWord([Name of Column 1],2, ",")
----------------------------------------
The result of the Above Function will be "b"

Now, in order to transform (normalize) my data in a table that looks like this (TableA)

Record 1 Column 1 = 23 Column 2 = a,b,c,d

Into A Query that looks like this

Record 1 Column 1 = 23 Column 2 = a
Record 2 Column 1 = 23 Column 2 = b
Record 3 Column 1 = 23 Column 2 = c
Record 4 Column 1 = 23 Column 2 = d


I write a Union Query:


----------------------------------------------------------
SELECT [Column 1], GetCSWord([Column 2],1,";") AS Data
FROM TableA
WHERE (((GetCSWord([Column 2],1,";")) Is Not Null) AND (([TableA.Column 2]) Is Not Null))

UNION SELECT [Column 1], GetCSWord([Column 2],2,";") AS Data
FROM TableA
WHERE (((GetCSWord([Column 2],2,";")) Is Not Null) AND (([TableA.Column 2]) Is Not Null))

UNION SELECT [Column 1], GetCSWord([Column 2],3,";") AS Data
FROM TableA
WHERE (((GetCSWord([Column 2],3,";")) Is Not Null) AND (([[TableA.Column 2]]) Is Not Null))

UNION SELECT [Column 1], GetCSWord([Column 2],4,";") AS Data
FROM TableA
WHERE (((GetCSWord([Column 2],4,";")) Is Not Null) AND (([TableA.Column 2]) Is Not Null))
------------------------------------------------------------

Hope this hleps some of you with the same question


Cheers

FGP