steve wrote:
I have a monthly safety slogan competition which requires back
checking to a list of already submitted slogans. This takes forerver
to do. I have 2 lists: this month's slogans and a master list of all
slogans.
Here is an example:
S1Master: An unsafe behavior can bring you down
S1Submitted: Unsafe behaviors can bring you down
I am thinking that after removing the plural "s", and then counting
the number of words that are in both sentences, as a percentage of the
total number of words. Percentage greater than 90% say would be listed
as a match.
6 words in 2 sentences the same = 12/13 words = 92.31%
therefore this is a matched pair.
I am trying to keep it simple but have enough accuracy, its not
terrible if a few slip by. Will the above method work and can I
achieve it without VBA. Please guide me.
Thanks
Steve
I don't know how you can do it without VBA. Also, do you want the
computer to figure this out for you or do you want to figure it out? To
keep it simple, some human interaction would be better.
For example, the 2 key words I saw in your example was "unsafe
behavior". You could run a query to find all master records that have
"unsafe behavior". Or you could create a query that finds all records
that contain "unsafe" and "behavior". See, here you are getting rid of
plurals. You control the keywords to search on.
Let's say you the master had a key value and the phrase. You have a
form to input 6 up to key words called Form1 with 6 keywords called
Key1..Key6. These Keywords that you type in have a default value of
Null. You could create a query to select the phrase.
Select Phrase, _
IIF(instr([Phrase],IIF(Not
IsNull(Forms!Form1!Key1),Forms!Form1!Key1,chr(0))) > 0,1,0) As Key1Cnt,
IIF(instr([Phrase],IIF(Not
IsNull(Forms!Form1!Key2),Forms!Form1!Key2,chr(0))) > 0,1,0) As Key2Cnt,
....etc.
What this does is it looks for the Key1 word in the phrase if there is a
Key1 value. Same for Key2. Does this for Key1 to Key6. If the key
word is found in the phrase, the value of the column is 1, if not found
or if the keyword to search is blank, the value is 0.
Save this as query1.
Now create another query. The first column is the phrase. The second
column is Key1Cnt + Key2Cnt + Key3Cnt...Key6Cnt. Click Show to off.
Set the criteria to >0. Sort in descending value. Save as query2. Run
this query.
This will exclude all records from the master table where no keywords
matched and present those that did have matches in the number of words
that did match. This method does not account for misspelled words.