im trying to come up with a query that finds all matching companies for 1 table to another. Thing is, even though the naming convention is different in each table they are the same company. What sql command can i use to to find similar company names?? I ellimanted all exact matches so far using this
where table1.company_name not in
from table 2
where table2.company_name = table2.company_name);
this query worked but it still gives me a list that is failry large. How do i come up with a sql query that fkinds all similar named companies?? here are ane xample of 2 companies that are the same but named differently:
SUNRISE GROWERS INC
There is no operator that will help you with this kind of match. 'LIKE' will work but in Access it is '*' for the wildcard. The problem is that 'SUNRISE GROWERS*' would match 'SUNRISE GROWERS INC' but the reverse is not true. 'SUNRISE GROWERS INC*' does not match 'SUNRISE GROWERS'. What you want to do can be very complicated and is accomplished using Soundex or a similar algorithm. I use similar software to find duplicates within a mailing list where Robert Smith and Bob Smith would be considered one person at a given address.
Do you have any other information to match on? An address? If not you might try gettting both lists in a common format and then try the match. For example drop all the 'INC.' and then match.
I put together a little hack-job to take care of a similar situation in my office. We receive a flat file from an external source that does not have any of the primary key information we have in our database, only text names. Some of the names don't match up, so I do something a little different. I pull two datasets into a couple grids (subforms in your case).
One grid is based on our data, the other the external data. When the user clicks a name from the external data, the grid with our data gets filtere liked so:
WHERE LEFT(ExternalDataField, 3) IN InternalDataField
The basic idea is to look at the first three characters of one dataset, then filter the other dataset by finding all names containing those first three letters anywhere in the name.
I had to do it this way for total compatability, for instance these two would be returned at the same time regardless of order:
SUN HOMES, LTD & LTD SUN HOMES
About 80% of the time there is only one matching name. For the rest of the time I simply pick the correct name out of the filtered dataset. I also included a manual filter so the user can type in whatever case-insensitive string they'd like to search for.
I did this in delphi using datagrids, you can do it in access using subforms just as easily. Come to think of it, it might be easier to use listboxes.
thanks for all the useful info but i don't have a copy of vb. What is the best way to handle this using sql queries. So far, i Managed to get the first word from the external data list using left and instr. However alot of the companies in the master list contain "the" or a period after "Inc". Im trying to think the best way to shorten this list through a sql filter. thanks guys