EmployedAgain
Programmer
Has anyone tried to use the Table Analyzer to dedupe and normalize data? I have an Access 2003 database that a department created and used for a while with one table, of course. Its almost 40,000 records and extremely inconsistent. Now they want it deduped and split out into 3 tables. The Analyzer is great, it was able to recognize names that differed only by a small typo, initial, etc. So I go through and decide which of those are ok to combine, which takes a good half hour, finish the wizard and after it runs a while comes up with the error "could not update, item locked". The db is local to my pc and nothing should be locked, any ideas?
I have tried multiple levels of find duplicate and grouping queries but there are still too many "fairly obvious" duplicates that it can't catch without some really intricate rules. If I could get the Table Analyzer to work it would take care of most of it and I could go from there. Otherwise I might have to resort to trying some VBA code on this one. I'd appreciate any ideas! The table has 3 main fields to evaluate : Name, Company, City. Names sometimes have prefix and suffix, sometimes have initials. Companies are not consistent and sometimes blank, citys are sometimes different between multiple name records. I've been able to do a few things to find out if the end of the name contains an Initial or Mr/Mrs but then there are ones like Smith, John A Jr Mr III vs Smith, John Jr or Smith, John Mr., with different Cities and companies...You see what I mean:>
TIA
B
I have tried multiple levels of find duplicate and grouping queries but there are still too many "fairly obvious" duplicates that it can't catch without some really intricate rules. If I could get the Table Analyzer to work it would take care of most of it and I could go from there. Otherwise I might have to resort to trying some VBA code on this one. I'd appreciate any ideas! The table has 3 main fields to evaluate : Name, Company, City. Names sometimes have prefix and suffix, sometimes have initials. Companies are not consistent and sometimes blank, citys are sometimes different between multiple name records. I've been able to do a few things to find out if the end of the name contains an Initial or Mr/Mrs but then there are ones like Smith, John A Jr Mr III vs Smith, John Jr or Smith, John Mr., with different Cities and companies...You see what I mean:>
TIA
B