=?Utf-8?B?UXNseA==?= said:
Extract and Generate table with one word per record
Hello!
I need help!
Table A is populated with frases in each record. I wish to generate table B
with all words from table A, but each word must be inserted in new line of
table B. This should be done for all records of table A.
Is there a way to do that ?
I appreciate any help. Thank you very much.
Qslx
The way that I would do this is to use the code on The Access Web at
http://www.mvps.org/access/strings/str0003.htm which allows you to parse a
character separated string into individual components (although it is set for
comma spearated, it is quite easy to replace the commas with spaces). You can
then use some code like that below to separate each phrase into individual
words:
Public Sub sSeparatePhrase()
On Error GoTo E_Handle
Dim db As DAO.Database
Dim rsA As DAO.Recordset ' Recordset of Phrases
Dim rsB As DAO.Recordset ' Recordset of Words
Dim intWords As Integer ' Number of words in the current phrase
Dim intLoop As Integer ' Loop counter
Set db = DBEngine(0)(0)
Set rsA = db.OpenRecordset("TableA")
Set rsB = db.OpenRecordset("TableB")
If Not (rsA.BOF And rsA.EOF) Then
Do
intWords = CountCSWords(rsA!Phrase)
For intLoop = 1 To intWords
rsB.AddNew
rsB!Word = GetCSWord(rsA!Phrase, intLoop)
rsB.Update
Next intLoop
rsA.MoveNext
Loop Until rsA.EOF
End If
sExit:
On Error Resume Next
rsA.Close
rsB.Close
Set rsA = Nothing
Set rsB = Nothing
Set db = Nothing
Exit Sub
E_Handle:
MsgBox Err.Description & vbCrLf & "sSeparatePhrase", vbOKOnly + vbCritical,
"Error: " & Err.Number
Resume sExit
End Sub
