I have a db in which each record contains a memo field that contains a list of what would best be described as "sub-records". In other words, each member of the list could constitute a record, with several fields, each identical except for the contents of the line from the list. For example, in the original db, a record could have 4 fields: FirstName, LastName, email and Hobbies, where Hobbies is a memo field with the persons hobbies list, each hobby on its own line. This db has thousands of records, or I would do this manually.

What I need to do is to go through each record and extract the entries from the memo field, one at a time, and create a new table with each memo field line as a separate record, including the other fields with it. So if John Smith has 3 hobbies listed, there would be three records in the new table, each having "John Smith" and the name of his hobby.

Is this possible? Is it reasonable? Am I living in a fantasy world in which I don't have to do hours of mind-numbing work to achieve my goal?

Thanks in Advance.

Yes, this is possible with the right structure. You would need this:

tblPerson
PersonID
LastName
FirstName
...

tblHobby
HobbyID
Hobby

tblPerson_Hobby
Person_HobbyID
PersonID - FK
HobbyID - FK

Then you can code the parsing of the memo field. What do you think the maximum number of characters are in the memo field? It is an odd choice and structure. I am going to look up the maximum field sizes of some variables and then I will post some code to do this. You will want to use code since you have to post to 3 tables at once.



mmcdonal

Look it up at: http://wrox.books24x7.com

Since the memo field may contain nulls, I am thinking, you could set it to Variant, since this variable can take about as much as you can throw at it.

(Chapter 8 - Executing VBA
Access 2003 VBA Programmer's Reference
by Patricia Cardoza, Teresa Hennig, Graham Seach and Armen Stein
Wrox Press © 2004)

I would do something like this (psuedo code)

Open a recordset on tblPerson - which I assuming you will keep.
Open a recordset on tblHobby
Parse through each hobby field using an array on each record.
   Dim HobbyArray As Variant
   Dim vHobby As Variant
   Dim i As Integer

   Do Until rs.EOF
   i = 0
   ReDim HobbyArray(0)
     vHobby = rs("HobbyField")
     HobbyArray = Split(vHobby, ", ") 'assuming comma delimited
       'Write all values in array in a loop here to tblHobby
     rs.MoveNext
   Loop

Remove duplicate hobby entries using a query here.
Then parse through the tblPerson table again, and match hobbies in separate arrays to the hobby table and link the PersonID there.

Anyway, how many records are we talking about?


mmcdonal

Look it up at: http://wrox.books24x7.com

I would say that the maximum number of total characters in any memo field is on the order of 1000 or so. Any given line of the field would be in the 50-75 range. These are just educated guestimates on my part, but I suspect that they are pretty close.

Here is the current structure:

tblMember
pkMemberID (autonumber - Primary Key)
FirstName (text - 100)
LastName (text - 100)
emailAdd (text - 255)
Hobbies (memo - default size)

What I would like to end up with is:

tblMemberHobby
pkMemberHobbyID
FirstName
LastName
emailAdd
Hobby

which would have a number of records for each person corresponding to the number of hobbies they listed.

Thank you for your assistance, and quick response.

There are 3868 records, each with from 5-15 hobbies listed. There should not be any duplicate hobbies, although if it's not too complicated to weed them out, I guess I should check for them.

I'm a bit vague on how to go about the parsing you mention. Can I set up some kind of code to go through the memo field of each record and separate it line by line?

Okay, here is code to separate out all the hobbies. I put this on a button on click event:

Dim rsP As ADODB.Recordset
Dim rsH As ADODB.Recordset
Dim sSQLP As String
Dim sSQLH As String
Dim HobbyArray As Variant
Dim vHobby As Variant
Dim i As Integer

sSQLP = "SELECT [Hobbies] FROM tblPerson"
Set rsP = New ADODB.Recordset
rsP.Open sSQLP, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

sSQLH = "SELECT * FROM tblHobby"
Set rsH = New ADODB.Recordset
rsH.Open sSQLH, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

rsP.MoveFirst
Do Until rsP.EOF
    ReDim HobbyArray(0)
    i = 0
    vHobby = rsP("Hobbies")
    HobbyArray = Split(vHobby, ", ")
    Do Until i = UBound(HobbyArray) + 1
        rsH.AddNew
        rsH("Hobby") = HobbyArray(i)
        rsH.Update
    i = i + 1
    Loop
rsP.MoveNext
Loop

rsP.Close
rsH.Close

This assumes that the hobbies are all written with a comma and a space between each one, like: "kayaking, sailing, crocheting"

I am working on the next bit to remove dupes.


mmcdonal

Look it up at: http://wrox.books24x7.com

Okay, this code removes the dupes in the Hobby table (look up table):

(I created a delete query to delete * from tblHobby to run between the taking of values and the replacing of values)

Dim rsH As ADODB.Recordset
Dim sSQLH As String
Dim sHobby As String
Dim HobbyArray As Variant
Dim i As Integer

sSQLH = "SELECT DISTINCT [Hobby] FROM tblHobby"
Set rsH = New ADODB.Recordset
rsH.Open sSQLH, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

ReDim HobbyArray(0)
i = 0
rsH.MoveFirst
Do Until rsH.EOF
    ReDim Preserve HobbyArray(i)
    HobbyArray(i) = rsH("Hobby")
i = i + 1
rsH.MoveNext
Loop
rsH.Close

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETEHobbies"
DoCmd.SetWarnings True

sSQLH = "SELECT * FROM tblHobby"
Set rsH = New ADODB.Recordset
rsH.Open sSQLH, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

i = 0
Do Until i = UBound(HobbyArray) + 1
    rsH.AddNew
    rsH("Hobby") = HobbyArray(i)
    rsH.Update
i = i + 1
Loop

rsH.Close


More to come...


mmcdonal

Look it up at: http://wrox.books24x7.com

Okay, here is the code that links the person with their hobbies in the tblPerson_Hobby junction table:

Dim rsP As ADODB.Recordset
Dim rsH As ADODB.Recordset
Dim rsPH As ADODB.Recordset
Dim sSQLP As String
Dim sSQLH As String
Dim sSQLPH As String
Dim HobbyArray As Variant
Dim vHobby As Variant
Dim sHobby As String
Dim i As Integer

sSQLP = "SELECT * FROM tblPerson"
Set rsP = New ADODB.Recordset
rsP.Open sSQLP, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

sSQLPH = "SELECT * FROM tblPerson_Hobby"
Set rsPH = New ADODB.Recordset
rsPH.Open sSQLPH, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

rsP.MoveFirst
Do Until rsP.EOF
    ReDim HobbyArray(0)
    i = 0
    vHobby = rsP("Hobbies")
    HobbyArray = Split(vHobby, ", ")
    Do Until i = UBound(HobbyArray) + 1
        sHobby = HobbyArray(i)
        sSQLH = "SELECT * FROM tblHobby WHERE [Hobby] = '" & sHobby & "'"
        Set rsH = New ADODB.Recordset
        rsH.Open sSQLH, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

            rsPH.AddNew
            rsPH("PersonID") = rsP("PersonID")
            rsPH("HobbyID") = rsH("HobbyID")
            rsPH.Update
        rsH.Close

    i = i + 1
    Loop
rsP.MoveNext
Loop

rsP.Close
rsPH.Close

Since this is one-time prcessing, this overhead is okay.

All of the entries took this data:

PersonID LastName FirstName Email Hobbies
1 Smith John swimming, sailing, kayaking
2 Doe John hiking, camping
3 Jones John skydiving, crocheting

And added this data to the tblHobby:

HobbyID Hobby
36 camping
37 crocheting
38 hiking
39 kayaking
40 sailing
41 skydiving
42 swimming

And created these records in tblPerson_Hobby:


Person_HobbyID PersonID HobbyID
1 Smith swimming
2 Smith sailing
3 Smith kayaking
4 Doe hiking
5 Doe camping
6 Jones skydiving
7 Jones crocheting

Was that what you wanted?







mmcdonal

Look it up at: http://wrox.books24x7.com