Access to automatically group words that appear frequently from a Title

[mycelium]

Hello,
I have a database that contains the title of many books.

I would like to create a grouping engine that automatically groups the books based on the words that appear in the title.

For example:

Book 1 : Little Princess of Fantasy
Book 2 : Little Birds of the Moon
Book 3 : Little Ducks on the Moon
Book 4 : Foxes in the Moon
Book 5 : Foxes and Cows
Book 6 : Foxes and Little Birds
Book 7 : Cleaning with Foxes
Book 8 : Enchanted Foxes


Group 1 : "Foxes" Appeared 5 times (and shows all the book titles)
Group 2 : "Little" Appeared 4 times (and shows all the book titles)
Group 3 : "Moon" Appear 3 times (and shows all the book titles)
Group 4 : "Birds" Appear 2 times (and shows all the book titles)


May I know how can I do this in Access or in Excel? Thank You.

[ranman256]

build a table of words to ignore:
the, and , of , with, etc...

run code to loop thru every word in the titles and post the results
[word], [title]
Little, Little Princess of Fantasy
Princess, Little Princess of Fantasy
of, Little Princess of Fantasy
Fantasy, Little Princess of Fantasy

once complete, use the tRemoveWords list to do just that.
delete from main table those in the tRemoveWords list

then count the results



Public Sub CountTitleWords()
Dim rst
Dim vTitl, vWord, vTxt
Dim i As Integer


DoCmd.SetWarnings False
Set rst = CurrentDb.OpenRecordset("select TITLE from tBookTitles")
With rst
While Not .EOF
vTitl = .Fields("Title").Value & ""
vTxt = vTitl
i = InStr(vTxt, " ")

While i > 0
If i > 0 Then
vWord = Left(vTxt, i - 1)
vTxt = Mid(vTxt, i + 1)
End If

GoSub PostResults

i = InStr(vTxt, " ")
Next
vWord = vTxt
GoSub PostResults

.MoveNext 'next book title
Wend
End With
Set rst = Nothing
DoCmd.SetWarnings True
End Sub


PostResults:
'post the keyword to the table
sSql = "Insert into tTitleWords ([word],[Title]) values ('" & vWord & "','" & vTitl & "')"
DoCmd.RunSQL sSql
Return
End Sub

[mycelium]

Hi,

Thank you for your help. However I am unable to run the code you provided as I am new to VBA and Access.

Below are the screenshots of what I have done. Please advice.

Thank you

https://drive.google.com/open?id=1CboMYMY9R--bRTngrYNKhExH3mg8ba5g

[ranman256]

I dont understand: " I am unable to run the code"

what doesnt work?
does it crash?
does it not complete?

[mycelium]

I got it up and running. Thank you very much. Appreciate your help :)