Consulting

Results 1 to 5 of 5

Thread: Access to automatically group words that appear frequently from a Title

  1. #1

    Access to automatically group words that appear frequently from a Title

    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.

  2. #2
    VBAX Tutor
    Joined
    Mar 2014
    Posts
    210
    Location
    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

  3. #3
    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=1Cb...NKhExH3mg8ba5g
    Attached Images Attached Images

  4. #4
    VBAX Tutor
    Joined
    Mar 2014
    Posts
    210
    Location
    I dont understand: " I am unable to run the code"

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

  5. #5
    I got it up and running. Thank you very much. Appreciate your help

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •