Match string data

[scriggs]

I have 2 tables with company names. The list of company names is input by seperate (unrelated) entities in each table. I need to compare the company name in each table and 1) automatically select the closest match and 2) present a list of other name sorted by matchness.

The data will be close each time and will have different spacing and spelling, etc. - i.e.

Table 1 Name Table 2 Name
Betfair 123 Betfair 123
Bet fair 123
Betfair123
123BetFair
BetFare 123
and so on....

I need the system to match table 1 against an entry of these in table 2

Any help greatly appreciated.

 

[CmptrTk]

I created a query in Access to match up the two fields where they were "like" one another. I'm not sure what you're asking to do in addition to that, but if it is to select other fields based on that criteria, of course you can choose them in the same query. So your SQL would look something like
SELECT table1.companyname, table1.Date, table2.companyname FROM table1, table2
WHERE (((table1.companyname) Like ([table2].[companyname])));

This was done with the query wizard in Access 2002. I'm not sure how you wanted to present the results though (i.e.form or report) Not sure how helpful this is but if you need to know more let me know.

 

[PHV]

Something like this ?
WHERE UCase(Trim(Table1.Name)) Like UCase(Trim(Table2.Name))
Depending of your version of access you may also try this:
WHERE UCase(Replace(Table1.Name," ","")) Like UCase(Replace(Table2.Name," ",""))

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[123FakeSt]

Mass Mailers like the one i work for spend thousands of dollars developing and/or using software to do this tp prevent incorrect and duplicate mailings.

It is called match-code (sometimes Matchcode, sometimes match code) software. You will probably need to purchase something unless you want to spend many hours creating all of the logic yourself. It compares the two strings a number of ways and then arrives at a 'score'. You can set the limit to whatevor service level you need.

Hope this helps,
Rusty

The early bird gets the worm, but the second mouse gets the cheese.
-------------------------------------
92.5% of statistics mean nothing.

 

[scriggs]

Thanks 123FakeSt, any clues on software which I could look at...