Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Fuzzy Matching in Access - usage

OpnSeasonOpnSeason Member Posts: 12
[b][red]This message was edited by OpnSeason at 2007-1-6 11:49:37[/red][/b][hr]
[b][red]This message was edited by OpnSeason at 2007-1-6 11:47:32[/red][/b][hr]
[b][red]This message was edited by OpnSeason at 2007-1-6 11:46:47[/red][/b][hr]
Link to the original post:
http://www.programmersheaven.com/c/MsgBoard/read.asp?Board=58&MsgID=331158&Setting=A9999F0001

Download at: http://www.kdkeys.net/forums/thread/6450.aspx

Here is how you can use it - I provide this example:

Tables and queries can be created in the MDE database.

Create a table with known good reference strings. I created this one - REF_LIST.

It has one field, REF_STRING (Text) with a length of 50, and indexed (No Duplicates). The field length can be set to a length that suits your requirements.

This is the content:


REF_STRING
Claw Hammer
Cold Chisel
Monkey Wrench
Nail Gun


Create another table with strings to match. I created this one - TEST_LIST.

It has one field, TEST_STRING (Text) with a length of 50, and indexed (Either No Duplicates or Duplicates Ok depending on the data). The field length can be set to a length that suits your requirements.

This is the content:


TEST_STRING
Claw Hamer
Claw Hammr
Clew Hammer
Clw Hammer
Cold Chisil
Cold Chisle
Cold Chissel
Cole Chisel
Monkey Wrnech
Monkie Wrench
Monky Rench
Nail Gn
Nail Gunn
Naill Gun
Nial Gun

Then create another table for the results. I created this one - RESULTS.

It has four fields, REF_STRING (same properties as in table REF_LIST), TEST_STRING (same properties as in table TEST_LIST), MATCH_VALU (Single, Fixed, 2 decimal places), and GOOD_MATCH (True/False).

This is the content from the results of the Match_Lists query.


REF_STRING TEST_STRING MATCH_VALU GOOD_MATCH
Nail Gun Nial Gun 0.92 No
Monkey Wrench Monky Rench 0.94 No
Monkey Wrench Monkie Wrench 0.94 No
Claw Hammer Clew Hammer 0.94 No
Cold Chisel Cold Chisle 0.94 No
Cold Chisel Cold Chisil 0.94 No
Cold Chisel Cole Chisel 0.94 No
Nail Gun Nail Gn 0.95 No
Monkey Wrench Monkey Wrnech 0.95 No
Nail Gun Nail Gunn 0.96 No
Nail Gun Naill Gun 0.96 No
Claw Hammer Claw Hamer 0.96 No
Claw Hammer Claw Hammr 0.96 No
Claw Hammer Clw Hammer 0.96 No
Cold Chisel Cold Chissel 0.97 No

SQL from the Match_Lists query:

INSERT INTO RESULTS ( REF_STRING, TEST_STRING, MATCH_VALU )
SELECT REF_LIST.REF_STRING, TEST_LIST.TEST_STRING, IsSimilar([REF_STRING],[TEST_STRING]) AS Expr1
FROM REF_LIST, TEST_LIST
WHERE (((IsSimilar([REF_STRING],[TEST_STRING]))>0.79));

Using this example you can populate the two tables, REF_LIST and TEST_LIST with strings that you need to compare and run the Match_Lists query.

The GOOD_MATCH field in the RESULTS table is for you or another human to determine if anything questionable is a good match for your purposes.
If it is found that any match with a value of at least .95 is a good match then an update query could be created to update the GOOD_MATCH field with true for all those with a value of >= .95.

Then a select query could be created to look at those matches that do not have a GOOD_MATCH to determine if they may be good matches.

Naturally the two tables may need a unique ID for the strings for better tracking and comparing.

If so, create them and have them appended to the RESULTS table as well in the Match_Lists query.

OpnSeason







Sign In or Register to comment.