Full Version: Compare 2 lists that are similar but not the same
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
swaziman1
Hi,

I have 2 tables of client lists from 2 different sources , I need to try and compare them to each
other to find which ones are in one list and not the other , etc...however the names of the clients are not exactly the same , one on list you could have client ABC Limited and on the other list client ABC Ltd , is there a way I can compare these in access perhaps using the IN function or something similar as my only other alternative that I see is going through each one individually in excel , the lists are about 5,000 names each so that would take for ever , please advise.

Thanks
AQM_UK
What you doing for the next six months? LOL

I do this.

I take the first five characters and then do a compare.

This throws up a load of weirdos, but it becomes very easy to see the ones that are the same.

I use 5 as this seems to work well.

ABC Limited and ABC Ltd will be matched as ABC L

Hope this helps.

Jim
swaziman1
Hi Jim,

Thanks for your swift reply , I've started doing somethign similar in excel using the first 6 then 8 then 10 then 12
characters , I was just hoping (praying) that access would provide an easier alternative but I suppose that was wishful thinking , thanks for you help.
AQM_UK
Well I am not saying it does not. The trouble is with this is that match data that does not match is very difficult.

MD Smith
M D Smith
M.D Smith
MD. Smith
M.D. Smith

The stick a Limited, Ltd, PLC, LLC as and when what ever takes the user and there you have it.

I had a person who liked to put a space in front over every field, so there is another variation.

Just very difficult.

Jim
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.