Split text in one field into multiple records

[Tiana Arylle]

Hi -

I have a requirement to take a phrase from one field and split each word
into a new record in a different table when that field on a form is updated.
This new table will be used as a search table, where each word is considered
a keyword (yes, even things like "a" and "the"). The search isn't being done
in access, so I don't have to worry about that piece.

I've been thinking that I need to trigger an append query from the form,
which would split the description into the relevant words (the length of the
description can vary) and then append the ID and each split word as a
separate record in table 2...but I can't get this to work. And, of course, I
need to call this from several forms. Help?

So, for example,

Table1 contains fields ID and Description.
ID: 123
Description: Sally loves dogs.

From a form, when changes are saved I need to take this information and put
it in
Table2.

Table2 contains ID and Keyword.
ID: 123
Description: Sally
ID: 123
Description: Loves
ID: 123
Description: Dogs

Thanks for your help!

 

[Jack Leach]

You can easily enough break the phrase down into seperate values for
individual words by using the Split function to load them into an array.
From there, some use of recordsets may be one way to get the information into
a new table.

If you have rsOrig which is based on a query that pulls the ID and Phrase,
and rsNew which you will add the records to, you can loop rsOrig and for each
record, add the appropriate records to rsNew...

(aircode...)
Public Function BreakToWords()
Dim rsOrig As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim vArr As Variant 'array to hold the split phrase
Dim i As Integer 'counter

Set rsOrig = CurrentDb.OpenRecordset( _
"SELECT fldID, fldPhrase FROM tableMain")
Set rsNew = CurrentDb.OpenRecordset("OtherTableName")

If rsOrig.Recordcount <> 0 Then

'loop the rsOrig records
rsOrig.MoveFirst
While Not rsOrig.EOF
'split the phrase on a space delimiter
vArr = Split(rsOrig("fldPhrase"), " ")

'loop the array (words) and add to rsNew
For i = 0 To UBound(vArr)
With rsNew
.AddNew
.Fields("fldID") = rsOrig("fldID")
.Fields("fldWord") = vArr(i)
.Update
.End With
Next

rsOrig.MoveNext
Wend

End If

rsOrig.Close
rsNew.Close
Set rsOrig = Nothing
Set rsNew = Nothing

End Function
(add error handling, etc)


This may not be the most efficient method, but it should work (and I don't
know how to do this using a query... they aren't strong points of mine).


If this is a one-time task (which I assume it is based on the nature of the
task), this should get it done for you. Keep in mind the possibility of null
values where the Phrase is, the above does not account for them.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Tiana Arylle]

Wow, thanks for the quick reply!

Unfortunately, this is not a one-time task - they want me to do this anytime
the description field is edited. I.e., if someone changes the description,
reparse the description string and then update the keyword table, matching on
the ID field. So, if the original description (present in the keyword table)
was "Sally loves dogs" but someone changes it to "sally loves cats" I need to
find that ID in the keyword table and change dogs to cats. If sally likes
dogs and cats, I need to add two new records - both with the ID, and the two
new words. If a new description is created with a new ID, I need to add new
records for the ID and each word in the new description.

Do you think the code you included will still work for that purpose?

~ T

 

[Jack Leach]

Just be careful to delete the old values pertaining to that particular ID if
someone changes the description of the main record... easier said than done.
For this reason, we generally recalc the data for each instance that it's
required rather than storing it, but I'm guessing that you don't have a whole
lot leeway in this case...

That said, you can make a quick modification to this function to pass the
main ID of the phrase to the function, and filter rsOrig to return only that
one phrase. Actually, you would at that point (being that we're only working
with one record), use DLookup (or Allen Browne's ELookup replacement for the
inferior DLookup - google it) to return that single phrase to be parsed.

Here's a modified version (still aircode...)



Public Function BreakToWords(lID As Long)

Dim rs As Recordset 'recordset of the search table
Dim vArr As Variant 'array for the words
Dim sPhrase As String 'phrase to break down
Dim i As Integer 'counter

sPhrase = Nz(ELookup("IDField", "MainTable", "IDField = " & lID), "")

'make sure a phrase was returned
If Len(sPhrase) = 0 Then
MsgBox "No Description found for given ID"
Exit Function
End If

'prep the recordset
Set rs = CurrentDb.OpenRecordset("SearchTableName")

'load the array
vArr = Split(sPhrase, " ")

'loop the array to the recordset
For i = 0 To UBound(vArr)
'add the records
With rs
.AddNew
.Fields("IDField") = lID
.Fields("WordField") = vArr(i)
.Update
End With
Next

'clear the rs
rs.Close
Set rs = Nothing

End Function




With this function declared as Public and placed in a Standard Module (not a
form or report's module), you can call it from anywhere else in your
project... for example, the Click event of a button from a form...

Private Sub cmdPrepSearchTable_Click()
BreakToWords Me!IDField
End Sub



--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Tiana Arylle]

Thanks Jack, after a little tweaking that code is working perfectly, and I
did go find Allen's eLookup function. Huzzah!

My next question is...how do I get it to delete the old values if it's a
change, not an insert? I've tried, but can't get anything to work. If it's
an existing record, there's no problem wiping the existing keywords for that
ID from the keyword table...we only need to store keywords for the current
state of the description. But I can't seem to bribe Access into working for
me today...Help?

Thanks again!
~ Tiana

 

[Tiana Arylle]

Nevermind, I fixed it in what might not be the best way, but it works.

I'm having Access run a delete query that looks for the ID that matches the
phrase I'm updating, then delete all records with that ID. Then it runs your
code, which beautifully parses the phrase and adds the new records to the
table. So it's not updating, it's deleting and adding, but works for my
purposes.

Thanks again!