query first two words

[a.mlw.walker]

Hi.
I have a list of 120000 entries, of companies in the UK. I need a query that will grab all the ones which have relevent similar names to others, like a duplicate query, but a bit more specific. For instance, First Choice UK, and First choice PLC are the same company, but have two entries. but I dont want to just scroll it would take ages. Is there a way to get a query to grab anything with matching words to other entries. Not necessarily the first two words, as my subject suggests, because the company name may not be two words, but whether there is any similarity in the name at all.

Thanks
alex

 

[Guus2005]

Use GetPart()

select * from table
where companyname like [URL="http://www.access-programmers.co.uk/forums/showthread.php?t=123923"]getpart([CompanyName]," ", 1)[/URL] & "*" & [URL="http://www.access-programmers.co.uk/forums/showthread.php?t=123923"]getpart([CompanyName]," ", 2)[/URL] & "*"

Looking for first two words in companyname string or something similar.
Never used it this way. Might be fun. Code was not compiled!

Enjoy!

 

[a.mlw.walker]

Thanks for replying. But how do i use that. Put it in the vb of a button? is compnayname meant to be the name of the table? what is the name of the textbox it is looking at to be similar to.

 

[Guus2005]

Put the GetPart function in a module and put the query in a query. Change the names of the table and fields. So that they match yours.

Enjoy!

 

[Dennisk]

I suspect that you require a soundex function.

 

[a.mlw.walker]

Hi Dennisk and Guus2005. Thanks for your help. I still struggled with it, and so went to the office help online.
It said that i could do a partial letter similarity query. starting like this:

select 0870.Company_Name
From 0870
where (((0870.Company_Name) In select [company_Name] from [0870] as tmp group by [company_Name] having count (*)>1)))
order by 0870.Company_Name;

This is the SQL.

But this doesnt work, apparently there is a syntax error. But if it did work, it goes on to say i can put something like ([company_name]7) and it will check the first 7 letters. Have you heard of sopmething like this??? Company name is the name of the field, and 0870 is the name of the table.

Also this only looks for dupicates in one table, I need to compare across two tables. (both with the field company_name)

Thanks

Alex