Match Word in Sentence (1 Viewer)

MTL021

New member
Local time
Today, 01:42
Joined
Sep 18, 2018
Messages
5
Hello All,

I am currently writing an access query to compare two tables and find matches. The only problem I have encountered is, I needs to find matches within sentences. I used the below, but unfortunately it is finding matches that contains the any of the characters and I want to find matches of 7 or more consecutive characters within the sentences


SELECT TEST.DELETION, DATABASE.*
FROM DATABASE, TEST
WHERE ((((InStr([TEST].[DELETION],[DATABASE].[A])))>1));

Any help on this would be greatly appreciated, as I have read a lot of articles and tutorials, but this seems to be a little more complex that I thought.
 

Minty

AWF VIP
Local time
Today, 05:42
Joined
Jul 26, 2013
Messages
10,355
Without seeing the data in the Database.A field I don't know how we can assist.
Surely if all your sample data is at least 7 characters long then you will only get those results?

Unless I've firmly grasped the wrong end of the bits of the tree...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:42
Joined
May 7, 2009
Messages
19,169
are you looking for a word within the string , or the string within the string.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 16:42
Joined
Jan 20, 2009
Messages
12,849
I concur with Minty. If TEST.DELETION has seven characters then it should be finding those matches.

I think you will get better performance with:
Code:
WHERE DATABASE.A Like "*" & TEST.DELETION & "*"

However you shouldn't call a table DATABASE as it is a Reserved Word.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:42
Joined
May 7, 2009
Messages
19,169
there are 2 queries on the sample db.
check which one suits your need.

view the Calculated column (Matches) in design view.
they are somewhat different.

see the RegExp in Module1.
 

Attachments

  • SamplematchCount.accdb
    456 KB · Views: 41

MTL021

New member
Local time
Today, 01:42
Joined
Sep 18, 2018
Messages
5
Hello All,

Sorry for the delay in getting back to you. What I need help with is - I am comparing two tables in Access and need to compare the Test table to the Extract table. In the Test table I am comparing the Deletion column to the Extract table and am looking for matches in either Column 58, 57, 56 and 54. I need the results to find words that are in sentences and want the query to find the matches of 7 consecutive characters or more. I have attached a sample document with the code I am currently using. The problem that I am having is that with my current code: if it finds 1 character it gives it back as a match, but I only want matches that are 7 consecutive characters or more.

I appreciate all the help I have gotten so far, and am excited to expand my Access skills.
 

Attachments

  • SAMPLE.TEST.accdb
    580 KB · Views: 48

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:42
Joined
Feb 28, 2001
Messages
27,001
I am still not quite wrapping my head around this. It sounds like you have a table with a column called "Deletion" and you have four columns of interest in an Extract table.

You have mentioned the attempt to find matches in the tables based on seven consecutive matching letters. So far, I understand at the overview level.

But where I am not clear is where the 7-letter match has to start. Is it always matching the first seven characters or do you want to match seven consecutive characters starting anywhere from either the Test or Extract tables? In other words, what text alignment constraints do you need for the two sets of text?
 

MTL021

New member
Local time
Today, 01:42
Joined
Sep 18, 2018
Messages
5
Hello,

What I am trying to compare is the Deletion Column (Test Table) to columns 58,57,56,54 (Extract Table) and find matches. In columns 58,57,56,54 all of those fields will have values of 11 characters. These 11 characters can be alphanumerical ( in the sample document provided they aren't, but in reality they can be).

I am trying to compare column 58,57,56,54 (Extract Table) to the Deletion Column in the Test table. In the test table, the values I am looking for might be found in a sentence (not always but it is a possibility I have to account for).

Essentially, what I am trying to do is compare fields 58,57,56,54 to the Deletion column and find matches of 7 or more characters.
 

Attachments

  • SAMPLE.TEST.accdb
    580 KB · Views: 39

jdraw

Super Moderator
Staff member
Local time
Today, 01:42
Joined
Jan 23, 2006
Messages
15,364
Can you show us, based on your sample data, what you want as a final result?
Also, where do you handle the match being 7 or more characters?

Perhaps the following: ( also exposes the length of extracted field)
Code:
SELECT TEST.deletion,extract.[58],Len([EXTRACT].[58])
FROM [EXTRACT], TEST
WHERE Len([EXTRACT].[58]) >=7
and (((InStr([TEST].[DELETION],[EXTRACT].[58]))>0));
union 
SELECT TEST.deletion,extract.[54],Len([EXTRACT].[54])
FROM [EXTRACT], TEST
WHERE Len([EXTRACT].[54]) >=7
and (((InStr([TEST].[DELETION],[EXTRACT].[54]))>0));
union 
SELECT TEST.deletion,extract.[56],Len([EXTRACT].[56])
FROM [EXTRACT], TEST
WHERE Len([EXTRACT].[56]) >=7
and (((InStr([TEST].[DELETION],[EXTRACT].[56]))>0));
union 
SELECT TEST.deletion,extract.[57],Len([EXTRACT].[57])
FROM [EXTRACT], TEST
WHERE Len([EXTRACT].[57]) >=7
and (((InStr([TEST].[DELETION],[EXTRACT].[57]))>0));
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:42
Joined
May 21, 2018
Messages
8,463
You show in the table
"There is a change to BBBBBBB and it effective 15 Dec"
And the value in the column is BBBBBBBBXXXX
Are you always looking for the first seven characters or any of the sub strings. In other words could you be looking for these.
BBBBBBX
BBBBBXX
BBBBXXX
BBBXXXX
....
 

MTL021

New member
Local time
Today, 01:42
Joined
Sep 18, 2018
Messages
5
Hello Majp,

I am always looking for the first 7 characters to be matched, I do not care about any substrings.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:42
Joined
Feb 28, 2001
Messages
27,001
The word "FIRST" in post #11 is still ambiguous, MTL021

Do you want the first seven characters of Deletion to match any sequence of seven characters in your numbered fields?

Or do you want any sequence of seven characters from Deletion to match the first seven characters of any of your numbered fields?

Or something else? The reason this is important is that we need to know the sense (direction, if you prefer) of the comparison and the "LIKE" operator wildcards that will be necessary to accomplish this feat.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:42
Joined
May 21, 2018
Messages
8,463
I am always looking for the first 7 characters to be matched

With that said I think you would modify the query
Code:
SELECT TEST.*, EXTRACT.*, "Field58" as FieldName
FROM [EXTRACT], TEST
WHERE InStr([TEST].[DELETION],left([EXTRACT].[58],7))>0
UNION
SELECT TEST.*, EXTRACT.*, "Field57" as FieldName
FROM [EXTRACT], TEST
WHERE InStr([TEST].[DELETION],left([EXTRACT].[57],7))>0
UNION
SELECT TEST.*, EXTRACT.*, "Field56" as FieldName
FROM [EXTRACT], TEST
WHERE InStr([TEST].[DELETION],left([EXTRACT].[56],7))>0
UNION SELECT TEST.*, EXTRACT.*, "Field54" as FieldName
FROM [EXTRACT], TEST
WHERE  InStr([TEST].[DELETION],left([EXTRACT].[54],7))>0
 

MTL021

New member
Local time
Today, 01:42
Joined
Sep 18, 2018
Messages
5
Hello,

Sorry for being ambiguous - what I am looking for is the first seven characters in field 58,57,56,54 to be matched against the Deletion column in the Test table. Now in the Deletion column, this value could be found in a sentence , but that won't always be the case. Essentially, I need the first 7 characters of columns 58,57,56,54 to be matched against all of the text strings in the Deletion Column an return a match of 7 characters of more.

If there is a match , to give it as a result and display the row from the Deletions column (Test Table) and the row from field 58,57,56,5.

Please note that in the real world use, the Extract table can have over 100,000 rows.
 

plog

Banishment Pending
Local time
Today, 00:42
Joined
May 11, 2011
Messages
11,613
To avoid ambiguity its best to demonstrate your issue with data. So provide 2 sets:

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what you expect to end up with when you feed in the data from A.

If you like, you can call the data in post #8 your A data. So using that show us what the B data would be.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:42
Joined
May 21, 2018
Messages
8,463
what I am looking for is the first seven characters in field 58,57,56,54 to be matched against the Deletion column in the Test table. Now in the Deletion column, this value could be found in a sentence , but that won't always be the case. Essentially, I need the first 7 characters of columns 58,57,56,54 to be matched against all of the text strings in the Deletion Column an return a match of 7 characters of more.

Unless I am misunderstanding, isn't that exactly what my query does? According to your post I do not have to check the length of field 58, 57, 56, 54 since they always eleven characters. I just need to match the first 7.
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:42
Joined
Jan 23, 2006
Messages
15,364
mtl021,

???This is what you said earlier
I want to find matches of 7 or more consecutive characters within the sentences
 

Users who are viewing this thread

Top Bottom