Good day to all the problem that I have is a table with the following info
COMPANY PRODUCTS
AAA A,B,C
BBB A,C,D,F
CCC D
DDD F,G,H,I,J,K,L
and I would like to see if it can be saved with some code in the following way:
COMPANY PRODUCTS
AAA A
AAA B
AAA C
BBB A
BBB C
ETC.
If someone could help me, I would really appreciate it.
Here I share a script that can give you an idea of how to do it.
The idea is to iterate the table where you have the values separated by comma, in each cycle, at your point you iterate the values separated by comma with split and insert it where you need. Applying this example with security solves it.
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
Greetings,
Thanks, Jorge Londoño, the code will stay the following way in case someone serves you.
Public Function FieldtoLinePro()
Dim strProductos() As String
Dim ID As Integer
Dim rs As dao.Recordset
Dim i As Integer
Dim strSql As String
Set rs = CurrentDb.OpenRecordset("COMPANY-T")
Do While Not rs.EOF
ID = rs!ID
strProductos = Split(rs!PRODUCTS, ",")
For i = LBound(strProductos) To UBound(strProductos)
strSql = "INSERT INTO [PRODUCTS-N] (C_ID,PRODUCTS) VALUES (" & ID & ",'" & strProductos(i) & "');"
CurrentDb.Execute strSql
Next i
rs.MoveNext
Loop
FieldtoLinePro = True
End Function