[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Access 2007 - Split a field with multiple inputs, into new records

Posted on 2009-04-14
5
Medium Priority
?
692 Views
Last Modified: 2013-11-27
Currently, I have an Excel Spreadsheet with a list of 30,000 e-mail subjects, addressees, sender, and subject. The "To" field often contains more than one address (sometimes as many as 50). I would like to be able to normalize the data in Access2007 as follows:

Current File:
Date |    From            | To                                                                  | Subject|
1/2/09 |xx@aol.com |tom@aol.com; bob@aol.com; jack@msn.com| Lunch this week

To be converted to:

1/2/09 |xx@aol.com |tom@aol.com |  Lunch this week
1/2/09 |xx@aol.com | bob@aol.com| Lunch this week
1/2/09 |xx@aol.com |jack@msn.com| Lunch this week

Any easy way to do this?

Thank You!
0
Comment
Question by:jareddxp
[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
5 Comments
 
LVL 15

Expert Comment

by:MNelson831
ID: 24142038
Here is some cool VBA code that I stumbled upon surfing the net one day:

Public Function ParseText(TextIn As String, x As Integer, stDelimiter As String) As Variant
On Error Resume Next

Dim Var As Variant
Var = Split(TextIn, stDelimiter, -1)
ParseText = Var(x)

End Function

When you call that function like this:

ParseText("Data1;Data2;Data3;Data4",3,";")

It returns "Data3" because data 3 is in the third slot.  So you could use it using a loop and an incrementing integer to return the first address, then the second, then the third, and so on and so on.
0
 

Author Comment

by:jareddxp
ID: 24142107
Thanks for the prompt response. Unfortunately, I don't know VBA well enough to put it to use. Any idea in how to accomplish in SQL or Access itself?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24142686
IMHO, VBA would be your best shot.

Can I presume that there will be more than one record having this format?
 
  "I don't know VBA well enough to put it to use."
Normally, I would agree, but if this is a one shot conversion, you will just be running the code.
What's to understand?
;-)

Let us know

JeffCoachman

JeffCoachman
0
 

Author Comment

by:jareddxp
ID: 24142980
If I am running and not writing, it works for me.... but so far no luck. Yes, there are about 5000 records..
I have been trying to get the code below to work, but nothing so far...I tried pasting the following code into the immediate window, with no results.

Where I am using: [IX] (as the message hash value), and [TO] as the sender field
I also have uploaded a file with a small number of records...

any/all help is GREATLY appreciated.
Dim dbCurr As DAO.Database
Dim rsInput As DAO.Recordset
Dim rsOutput As DAO.Recordset
Dim lngLoop As Long
Dim strTO As String
Dim varTos As Variant
 
Set dbCurr = CurrentDb()
  Set rsInput = dbCurr.OpenRecordset("SELECT IX, To FROM NameSplit")
  Set rsOutput = dbCurr.OpenRecordset("SELECT IX, To FROM NameSplit2 WHERE False")
  Do While rsInput.EOF = False
    varTos = Split(rsInput!To, ";")
    For lngLoop = LBound(varTos) To UBound(varTos)
      strTO = Trim(varTos(lngLoop))
      rsOutput.AddNew
      rsOuput!SKU = rsInput!IX
      rsOutput!Option = Tos
      rsOutput.Update
    Next lngLoop
    rsInput.MoveNext
  Loop
  rsInput.Close
  Set rsInput = Nothing
  rsOuput.Close
  Set rsOutput = Nothing
  Set dbCurr = Nothing
End Sub

Open in new window

trialdatabase.mdb
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 24143451
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 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