[AccessD]

Good lord.  Different by up to 2 characters per digit?  That would
yield a range of results so large as to be useless.

That said, I would make the assumption that no more than one typo per
SSN was likely to be made.  That measn only one of the three SSN
chunks is likely to be wrong, while the otehr two are correct.

Then I would do three searches and UNION the results.

For example, given keyed input of 987-65-4321...

Search 1:  SELECT FirstName, LastName, SSN FROM tblBLah WHERE SSN
Like *-65-4321

Search 2:  SELECT FirstName, LastName, SSN FROM tblBlah WHERE SSN
Like 987-*-4321

Search 3:  SELECT FirstName, LastName, SSN FROM tblBlah WHERE SSN
Like 987-65-*

You could keep the search results in a temp table (slow) or a bunch
of recordsets (still slow, but less so).  Then take the results of
all three searches and UNION SELECT them to come up with a master
list of SSNs that are somewhat similar.

You might want to do all this searching in a dll outside the Access
environment for the sake of speed.

This is completely off the top of my head, on the fly, spur of the
moment, what have you.  Please feel free to blast holes in my idea.

-Christopher-



---- Original Message ----
From: jcolby at colbyconsulting.com
To: AccessD at databaseadvisors.com, 
Subject: RE: [AccessD] "fuzzy logic" search
Date: Tue, 21 Oct 2003 20:05:43 -0400

>My client is having difficulties where the data input people are 
>entering
>duplicate claims because wrong names or SSNs are entered.  He's 
>asked how
>difficult it would be to do a search for the names entered (seems 
>irrelevant
>if it is wrong) but then has asked how difficult it would be to do a 
>"fuzzy
>logic" search for SSNs that are different by up to 2 characters.  I 
>don't
>even have a clue how to attack something like that.  Any ideas 
>(other than
>"out the door, no parachute")?
>
>John W. Colby
>www.colbyconsulting.com
>
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>