• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

Multiple Cell Contents To Individual Rows/Records

Dear Experts

I have multiple contents in 1 Cell/Field which i would like to put them in individual rows, the items are delimited by the ampersand. I've illustrated my problem in a visio...PDF.

Thank you much Experts!
0
Dare626
Asked:
Dare626
  • 2
  • 2
  • 2
2 Solutions
 
Dare626Author Commented:
Here is the attachment.
Visio-Cells-Items-To-Rows-Proble.pdf
0
 
Rey Obrero (Capricorn1)Commented:
you will need vba codes to do that

dim rs1 as dao.recordset, rs2 as dao.recordset, vArr() as string, j

set rs1=currentdb.openrecordset("table1")
set rs2=currentdb.openrecordset("table2")

do until rs1.eof
     varr=split(rs1![Measure],"&")
     for j =0 to ubound(vArr)
         rs2.addnew
         rs2!Mem_NO=rs1!Mem_NO
         rs2![Measure]=varr(j)
         rs2.update
     next

rs1.movenext
loop
0
 
MMTadminCommented:
Assuming you are savvy with talking to databases?

Public Function SplitMyValues

Dim rs as Recordset
Dim Measures() as String
Dim k as Long

'get the rs of Table1 here

With rs
Do until .eof
Measures = Split(!Measure, " & ")
For k = 0 to ubound(Measures)
sql = insert into Table2(Mem_No, Measure) VALUES (!Mem_No, Measures(k))
cn.execute sql
Next
.movenext
Loop
End with
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
MMTadminCommented:
capricorn1 >> split character =  " & " unless you want to trim varr(j)
0
 
Rey Obrero (Capricorn1)Commented:
oh yeah , didn't notice the space, thanks


dim rs1 as dao.recordset, rs2 as dao.recordset, vArr() as string, j

set rs1=currentdb.openrecordset("table1")
set rs2=currentdb.openrecordset("table2")

do until rs1.eof
     varr=split(rs1![Measure],"&")
     for j =0 to ubound(vArr)
         rs2.addnew
         rs2!Mem_NO=rs1!Mem_NO
         rs2![Measure]=trim(varr(j))
         rs2.update
     next

rs1.movenext
loop
0
 
Dare626Author Commented:
Thank you Capricorn for the fast post and MMTAdmin for expanding on capricorns solution with. I appreciate both of your time answering my question...worked perfect!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now