Fuzzy mapping_Instr Query

[google]

Hi guys, Recently I was given a task to fuzzy map two set of keywords data where the mapping for following criteria should be done.



1 ) Direct Match
2 ) find the content of Table A into content of Table B ( whole data and various lengths)
3 ) find the content of Table B into content of Table A ( whole data and various lengths)


I am not a programmer and i can't think of anyway to accomplish the above mentioned tasks other than using the "Instr" function in Access. Access 2007 cant really handle the query as output is really large. It freezes for hours while trying to save the output as a new table. First sample table has 30,000 keywords and second sample table have 1,60,000 keywords.

I have uploaded the sample data in my google account. Please suggest me a way to accomplish the mapping criterias mentioned above

https://drive.google.com/file/d/0B9N...ew?usp=sharing

Thanks a lot in advance.

[google]

Guys, No ideas on my topic??

[CJ_London]

No ideas on my topic??

Don't think anyone quite understands what you are asking. What does this mean for example?

find the content of Table A into content of Table B ( whole data and various lengths)

Also people are hesitant about downloading data.

So suggest provide some data in your post for Tables A and B and what you require as an output.

[orange]

google,

As Ajax has said, I also don't think many readers have any idea about what your post is asking. It is not clear.
I recommend you give us some definition of "Fuzzy map" in simple terms, and an example or two showing a set of
keywords and maps; and samples of maps of whole data and various lengths.

To get focused responses on this or any forum the readers have to understand your issue (and the clearer the better).
So try again to tell us the requirement --simple clear terms with examples.

Also, as has been stated by others, readers are reluctant to download files -especially from another source.

You may get some ideas from researching : Levenshtein distance, FuzzyMatching, Thesaurus, Synonym
Here is a UA thread that may provide some insight.

Good luck with your project

[google]

Don't think anyone quite understands what you are asking. What does this mean for example?






Also people are hesitant about downloading data.


So suggest provide some data in your post for Tables A and B and what you require as an output.

google,


As Ajax has said, I also don't think many readers have any idea about what your post is asking. It is not clear.
I recommend you give us some definition of "Fuzzy map" in simple terms, and an example or two showing a set of
keywords and maps; and samples of maps of whole data and various lengths.


To get focused responses on this or any forum the readers have to understand your issue (and the clearer the better).
So try again to tell us the requirement --simple clear terms with examples.


Also, as has been stated by others, readers are reluctant to download files -especially from another source.


You may get some ideas from researching : Levenshtein distance, FuzzyMatching, Thesaurus, Synonym
Here is a UA thread that may provide some insight.


Good luck with your project

I understand i got a weird username and people are hesitant to download the access file as it was uploaded to Google Docs, I am not able to upload the access file to the forum, Not sure why.

I am sorry if my request descriptions are so blunt. I am used to these terms internally. I will try my best to explain my case below.

Here are the sample contents of both tables.

Table A

Inch

kill

selfie

fortune

Table B

Finch

self

fortune 500

killer

As both set of data don't have a unique ID to map, I have used the Instr function which checks each keyword from table A to all keywords from Table B. From the example Table A has 4 keywords and Table B has 4 keywords and If i use Instr function the result will be 16. I have attached a picture below of the result from different mappings using the Instr functions for the sample data.


Terms Used :

Direct Match	Same content is available at both tables
Table A whole data into Table B	Table A content is used to find if they are available as a partial or full match with data in Table B
Table B Whole data into Table A	Table B content is used to find if they are available as a partial or full match with data in Table A
Table A(5 characters) into Table B	First 5 characters of table A content is used to find if they are available as a partial or full match with data in Table B
Table B(5 characters) into Table A	First 5 characters of table B content is used to find if they are available as a partial or full match with data in Table A

Instr function cannot be practically used in my case as i have 30k data in a table and 160k data in second table. I am looking for a alternate method in access or SQL to achieve the result set from the picture.

Thanks for checking my Query.

[CJ_London]

Much clearer!

Use a Cartesian query (no joins) with the two tables

SELECT *, A.Col1=B.Col1 as DirectMatch, instr(B.Col1,A.Col1)>0 as AinB, instr(A.Col1,B.Col1)>0 as BinA, instr(B.Col1,iif(len(A.Col1)>5,left(A.Col1,5),A.Col1)>0 as AinB5, instr(A.Col1,iif(len(B.Col1)>5,left(B.Col1,5), B.Col1)>0 as BinA5
FROM TableA A, TableB B

[CJ_London]

further though if you don't want to use instr is to use like e.g.

instr(A.Col1,B.Col1)>0 as BinA

would become

B.Col1 like "*" & A.Col1 & "*" as BinA

However the initial "*" means that access cannot use indexing so there is unlikely to be any improvement in performance

[google]

Thx Ajax, I will check the solution you have provided.