How to split two columns into new rows??

[Patrik]

Hi everybody,

I have an Access table were I have two columns... like this:

Column1 Column2
==========================================
AB12345C U1234, U2345, U3456
EF67890_G U4321
HIJ12121 U9876, U6789, U1480, U4321
K98798P_P U9999, U2315

The first column has unique fields (as string) and the second column
consists of fields (as string) which can have one or more 'Uxxxx'
separated with a ", ". Now I need somehow to get the table to look
like this:

Column1 Column2
========================
AB12345C U1234
AB12345C U2345
AB12345C U3456
EF67890_G U4321
HIJ12121 U9876
HIJ12121 U6789
HIJ12121 U1480
HIJ12121 U4321
K98798P_P U9999
K98798P_P U2315

So, I want to separate the second column to new rows, if there are
more than just one Uxxxx. It's hard to explain but I think that the
graph above will give the best hint what I'm trying to achive.

I have tried to find all kind of solutions, VBA-functions, queries,
but there have been no luck at all... so if somebody have any ideas or
solutions to my problem I would be more than grateful!

Thanx in advance! =)

 

[Douglas J Steele]

I don't think this can be done as a simple query: you're going to have to
use VBA for it.

Assuming you're using Access 2000 or higher, one approach would be:

Dim dbCurr As DAO.Database
Dim rsIn As DAO.Recordset
Dim intLoop As Integer
Dim strSQL As String
Dim varColumn2Values As Variant

Set dbCurr = CurrentDb()
Set rsIn = dbCurr.OpenRecordset("SELECT Column1, Column2 FROM Table1")
With rsIn
Do While .EOF = False
varColumn2Values = Split(!Column2, ",")
For intLoop = LBound(varColumn2Values) To UBound(varColumn2Values)
strSQL = "INSERT INTO Table2 (Column1, Column2) " & _
"Values ('" & !Column1 & "', '" & varColumn2Values(intLoop) &
"')"
dbCurr.Execute strSQL, dbFailOnError
Next intLoop
.MoveNext
Loop
End With

rsIn.Close
Set rsIn = Nothing
Set dbCurr = Nothing

Note that I'm using DAO. If you're using Access 2000 or 2002, you might have
to add a reference, as it's not set by default.

 

[Patrik]

How do I use that function and how do I run it in a query?? When I
have put the function in the VBA I get errors complaining that the
OpenRecordset has too few arguments, it is expected to have 2
arguments?? I made the reference in Access like you said so it doesn't
complain about the "DAO.Database".

 

[Douglas J Steele]

As I said, you can't run it in a query: SQL doesn't have any way of
generating multiple rows from one.

Did you change the SQL inside the OpenRecordset statement to whatever's
valid for your situation?

Post the exact code you're using, and the details of the two tables
involved.

 

[Patrik]

Wohoo... I found another solution and I got it to work =) Here is the
link for the solution if someone is having the same problems as I had:

http://groups.google.fi/groups?hl=fi&lr=&selm=#[email protected]

But, thanx anyway, Douglas, for your help and your time...
unfortunately I didn't get your solution to work... maybe next time =)