Full Version: Compare 2 lists that are similar but not the same
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
swaziman1
Hi,
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
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.
D 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.