UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Find Multiple Whole Keyword/phases From One Table, As A Partial Match In Another Table, Access 2016    
 
   
darls15
post Aug 27 2019, 06:31 PM
Post#1



Posts: 3
Joined: 27-August 19



Hi All

I was hoping someone might be able to help me with how to achieve the following. I’ve tried searching for a solution and am having no success.

I have two tables that are similar to the ones below and that have no relationship.

Table A:
ID Sentence
1 The bird, cat and dog are his friendly pets.
2 My dog really likes to go to the dog park.
3. I think I might take my umbrella just in case it rains.
4 I have a very large, colourful and noisy bird.

Table B:
bird
cat
dog
dog park
sheep
pets
park

If it's possible, I need help with a query or VBA that will ultimately give me the results below:

ID Sentence 1stMatch 2ndMatch 3rdMatch 4thMatch
1 The bird, cat and dog are his friendly pets. bird cat dog pets
2 My dog really likes to go to the dog park. dog dog park
3. I think I might take my umbrella just in case it rains.
4 I have very large, colourful and noisy bird. bird

If there's no keyword/phrase in Table B that are found in the Table A, I would still like the ID and sentence to appear in the results with all columns NthMatch columns blank – as in sentence 3.

To better view how the results should be displayed, I've attached an example.

Any help or advice would be appreciated.

Many thanks
This post has been edited by darls15: Aug 27 2019, 06:32 PM
Attached File(s)
Attached File  Example.zip ( 7.55K )Number of downloads: 1
 
Go to the top of the page
 
MadPiet
post Aug 27 2019, 06:45 PM
Post#2



Posts: 3,331
Joined: 27-February 09



Use INSTR() and a cartesian join in a query?

Okay, this is perplexing... Here's my attempt to make this work:

SELECT xlsData.ID, SearchTable.KeywordToSearchFor, xlsData.Sentence, InStr(1,[Sentence],[KeywordToSearchFor]) AS IsFound
FROM SearchTable, xlsData
ORDER BY SearchTable.KeywordToSearchFor;

I ran an update on the Sentence column to remove commas etc that are unnecessary. I get some matches that work, but a bunch don't.
This post has been edited by MadPiet: Aug 27 2019, 07:03 PM
Go to the top of the page
 
MadPiet
post Aug 27 2019, 07:56 PM
Post#3



Posts: 3,331
Joined: 27-February 09



Okay, there was something funky with the data in my table... so it didn't always work... Here's the fixed query:

SELECT SearchTable.KeywordToSearchFor, xlsData.Sentence, InStr(1,[Sentence],[KeywordToSearchFor]) AS There
FROM SearchTable, xlsData
WHERE (((InStr(1,[Sentence],[KeywordToSearchFor]))>0));

So you don't have to parse everything.

How about this one:

SELECT xlsData.ID, SearchTable.KeywordToSearchFor, xlsData.Sentence
FROM xlsData, SearchTable
WHERE (((xlsData.Sentence) Like "*" & [SearchTable]![KeywordToSearchFor] & "*"));

Looks like it works... =) But if you have LOTS of data, this could be brutal slow. If that's the case, you'd want to parse the sentence into words and index it.
Go to the top of the page
 
darls15
post Aug 27 2019, 10:22 PM
Post#4



Posts: 3
Joined: 27-August 19



Hi MadPiet, thanks so much for this. I'm trying it out now on my original dataset and you're right, it's painfully slow as there is about 500,000 records in the "sentences" and over 26,000 "keywords" to search.
Can you please explain how I would go about doing "parse the sentence into words and index it"?
Many thanks
Go to the top of the page
 
MadPiet
post Aug 27 2019, 11:18 PM
Post#5



Posts: 3,331
Joined: 27-February 09



Can you please explain how I would go about doing "parse the sentence into words and index it"?

In a word, you need VBA for this, because then you can use SPLIT()
Near forgot something important. I cheated and replaced all the periods and commas in the strings with nothing. If I were doing it in my code, it should be

Here's the code that does the splitting work...
1. (xlsData is just what I called my source table... the one with the full sentences in it).
2. "KeyWordsInSentence" is the table I'm inserting the parsed words into (along with the ID# of the sentence from xlsData).

CODE
Public Sub ParseKeywords()
    Dim rsSrc As DAO.Recordset
    Dim rsKeywords As DAO.Recordset
    Dim varWords As Variant
    Dim i As Integer
    
    Set rsSrc = CurrentDb.OpenRecordset("xlsData", dbOpenTable, dbForwardOnly)
    Set rsKeywords = CurrentDb.OpenRecordset("KeywordsInSentence", dbOpenTable, dbAppendOnly)
    
    '-- loop through the entire source table, and split the sentences into individual words.
    Do Until rsSrc.EOF
        varWords = REPLACE(
        varWords = Split(rsSrc.Fields(1), " ")
        For i = LBound(varWords) To UBound(varWords)
            With rsKeywords
                .AddNew
                .Fields("Keyword") = varWords(i)
                .Fields("SentenceID") = rsSrc.Fields(0).Value
                .Update
            End With
        Next i
        rsSrc.MoveNext
    Loop
    
    rsSrc.Close
    rsKeywords.Close
    Set rsSrc = Nothing
    Set rsKeywords = Nothing
End Sub


I've attached the database.... all the code is in it. There are a bunch of queries.
InstrTest basically tests using InStr() to find a string inside another. Would be painfully slow.
MatchUsingCrossjoin - also painful, because you'll get 26,000 * 50,000 possible combinations. Yuck.

This is the SQL for "MatchUsingParsedValues":

CODE
SELECT xlsData.Sentence, KeywordsInSentence.Keyword
FROM SearchTable INNER JOIN (xlsData INNER JOIN KeywordsInSentence ON xlsData.ID = KeywordsInSentence.SentenceID) ON SearchTable.KeywordToSearchFor = KeywordsInSentence.Keyword;


The KEY for this is to INDEX!!! Index the SentenceID columns (in both the "sentence" table and the one in the "KeywordsInTable" table.) Otherwise, you're going to get terrible performance.

If someone knows of another way to get around the problem with words/phrases like "bird dog", I'd love to know it... I think you'd have to have a table of those words and then search the "sentences" column for them and replace the space between the two words with an underscore... in both the "sentences" column and in the "Keywords" column/table. Other than that, I'm out of ideas.

Hope this helps... Post if you have more questions.

Go to the top of the page
 
MadPiet
post Aug 27 2019, 11:20 PM
Post#6



Posts: 3,331
Joined: 27-February 09



"once more, with <strikethrough>feeling</strikethrough>attachment"
Attached File(s)
Attached File  MatchingWords.zip ( 43.82K )Number of downloads: 5
 
Go to the top of the page
 
darls15
post Sep 1 2019, 05:14 AM
Post#7



Posts: 3
Joined: 27-August 19



Hi MadPiet, my apologies for the late reply. Thanks for your help and for the sample database. I've downloaded it and hopefully will be able to get the desired results with it. smile.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th October 2019 - 01:38 PM