vba code behnd button to parse table

[rfuscjr via AccessMonster.com]

I have some data stored in a table but not in a user friendly format. I want
to transform it a new table. I want to accomplish this with some code behind
a button on a form.

Here is the structure of the current and desired tables as well as a sample.
Can someone help with the code?

CURRENT TABLE:
tblNasty
tblNasty.Id
tnlNasty.List


NEW TABLE
tblNice
tblNice.Id
tblNice.ListItem
tblNice.Sequence


Example Convert:

Id List
111 5^668^777^65^6
222 77^82
333 6

To:
Id List Sequence
111 5 1
111 668 2
111 777 3
111 65 4
111 6 5
222 77 1
222 82 2
333 6 1

 

[Alex Dybenko]

Hi,
something like this, some air code just to show the idea:

set rst=currentdb.openrecordset("select * from tblNasty", dbopenforwardonly)
do until rst.eof
a=split(rst!List,"^")
for I=0 to ubound(a)
currentdb.execute("insert into tblNice (id, listitem, sequence)
Values(" & rst!id & "," & a(I) & "," & I & ")"
next i
rst.movenext
loop

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[rfuscjr via AccessMonster.com]

what is split?

Hi,
something like this, some air code just to show the idea:

set rst=currentdb.openrecordset("select * from tblNasty", dbopenforwardonly)
do until rst.eof
a=split(rst!List,"^")
for I=0 to ubound(a)
currentdb.execute("insert into tblNice (id, listitem, sequence)
Values(" & rst!id & "," & a(I) & "," & I & ")"
next i
rst.movenext
loop

I have some data stored in a table but not in a user friendly format. I
want

[quoted text clipped - 34 lines]

222 82 2
333 6 1

 

[rfuscjr via AccessMonster.com]

ok, I see you can use it in vba but not in a regular query...will play with
this...thanks!

what is split?

Hi,
something like this, some air code just to show the idea:

[quoted text clipped - 14 lines]