Solved

vba routine function needed

Posted on 2014-01-29
10
435 Views
Last Modified: 2014-01-29
hi, i am looking for a function that should do the following:

table1 has a column note
table2 has a column note

data in table1 is saved in one column separated by commas ("a,b,c,d")

function should store the above info in table2 in 4 records.
0
Comment
Question by:bfuchs
  • 4
  • 4
  • 2
10 Comments
 
LVL 4

Expert Comment

by:Jack Leach
ID: 39819152
Hi,

I can only hope that this is being done in an effort to properly normalize...

Try something like this (untested, add error handling, adjust types and names etc):

Sub ParseData()

  Dim rs As DAO.Recordset
  Dim v As Variant

  Set rs = CurrentDb.OpenRecordset("Table1")

  While Not rs.EOF

    'split the values into an array
    v = Split(rs("YourField"), ",")
    'insert the values into the new table
    CurrentDb.Execute "INSERT INTO Table2 (FieldName) VALUES (""" & CStr(v(0)) & """);"
    CurrentDb.Execute "INSERT INTO Table2 (FieldName) VALUES (""" & CStr(v(1)) & """);"
    CurrentDb.Execute "INSERT INTO Table2 (FieldName) VALUES (""" & CStr(v(2)) & """);"
    CurrentDb.Execute "INSERT INTO Table2 (FieldName) VALUES (""" & CStr(v(3)) & """);"
    DoEvents

    rs.MoveNext
  Wend

  rs.Close
  Set rs = Nothing
End Sub

Open in new window


You can probably manage the same thing through pure SQL as well, though this might give you a better "visual" on what's actually going on.

Note that this code makes some assumptions, particularly a) every row in Table1 has values in that field, and b) there's always exactly 4 values which are comma delimited.  Variations on this will require more logic.

hth
-jack
0
 
LVL 4

Accepted Solution

by:
Jack Leach earned 500 total points
ID: 39819177
Here's one that will handle varying numbers of data, though still reliant on a comma delimiter:

Sub ParseData()

  Dim rs As DAO.Recordset
  Dim v As Variant
  Dim i As Integer

  Set rs = CurrentDb.OpenRecordset("Table1")

  While Not rs.EOF

    If Not IsNull(rs("YourField") Then

      'split the values into an array
      v = Split(rs("YourField"), ",")

      'insert the values into the new table
      For i = 0 To Ubound(v)
        CurrentDb.Execute "INSERT INTO Table2 (FieldName) VALUES (""" & CStr(v(i)) & """);"
      Next i

      DoEvents

    End If

    rs.MoveNext
  Wend

  rs.Close
  Set rs = Nothing
End Sub

Open in new window

0
 
LVL 3

Author Comment

by:bfuchs
ID: 39819243
Hi Jack,

thanks for replying, I will test that and let you know.

btw,
You can probably manage the same thing through pure SQL as well

just wonder how is that possible, i think would go for that..
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39819435
Field has 3 commas. Is this a fixed format?
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39819498
hi,
no, it can have 0 to 8 commas.

e.g
a
a,b
a,b,c
a,b,c,x,y,z
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 4

Expert Comment

by:Jack Leach
ID: 39819507
the second function I posted will handle any amount of comments... from 0 to 32,000+ (or change i to a Long instead of Integer to get many more)
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39819525
hi Jack,
It seems that your suggestion would do the job, just waiting to hear if there is a sql solution for that, as i assume that will work much faster...

However if no one comes up with something i will definitely accept that,

Thanks very much for your quick response
Ben
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39819540
If format is not fixed, then a sub as Jack Leach suggested is fine.
0
 
LVL 4

Expert Comment

by:Jack Leach
ID: 39819578
I'm not sure of a SQL solution offhand. While I think it could be done, it would be a bit on the advanced side and I'm not as good with SQL to whip a solution like that off the top of my head the way I can with VBA.
0
 
LVL 3

Author Closing Comment

by:bfuchs
ID: 39819873
Thanks again Jack,
Please if you do find an answer on the sql path, share it with us..
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now