Solved

vba routine function needed

Posted on 2014-01-29
10
443 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 4

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
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

 
LVL 30

Expert Comment

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

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
 
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 4

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 4

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

735 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