Validating and Updating similar string field values

[b_rye_chan]

So I'm working with a company that is a little late to the database game and I've been tasked with creating a relatively simple Contacts database. Previous to this, I have close to zero experience with Access (but I am familiar with VBA) so tell me if I'm completely off the mark with what I'm trying to do.



I was given a variety of excel spreadsheets containing company contacts from different employees (all formatted differently by the way). Easy enough though, it doesn't take long to make the excel fields match my database fields and import. Of course there were a variety of validation rule issues but the only thing I can't 'standardize' is the field containing the name of the Company of a contact. Also, the database will have 10k+ records (I am working with only one contact list containing 475 records right now for testing purposes).

Some examples of my problem:
Example 1:

Contact1 works for Bank of America, and the value in [Company] = Bank of America.

Contact 21 works for Bank of America as well, but the value in [Company] = Bank of America. N.A

Example 2:

Contact 23 works for Pacific Gas & Electric Company and the value in [Company] = Pacific Gas & Electric Company
Contact 312 works for same company, and the value in [Company] = Pacific Gas and Electric Company
Contacts XYZ may include, P.G.E. or PG&E or Pacific Gas & Electric.

Is there a way to create either a query or VBA function that could update these field values to a universal value per company?
Right now, my method of doing this is running query 'SELECT DISTINCT tblContacts.Company FROM tblContacts ORDER BY tblContacts.Company' and editing manually. I can't think of any possible solutions than this, but hopefully one of you guru's has a trick up his sleeve to make this a little less tedious. Thanks in advance for any input/advice!!

P.S. If anyone has any ideas on building a validation rule to prevent this from happening with future input, that would be awesome as well.

[CJ_London]

there a many permutations for potential matching. I recently dealt with 21 variations of MacDonalds (out of 50 odd instances in 100,000 records). Regret the only way is by visual inspection. It helps to create a form to make the job easier.

You can of course write queries to help - match on first word or last word, use of the like criteria etc, but doubt you will find a solution to fit all the variations.

You can also investigate using soundex or simil algorithms to find names which sound the same. Plenty of threads out there on the subject - here is one for example

http://www.access-programmers.co.uk/...hlight=soundex

again, won't be exact - it wont match Pacific Gas and Electric Company to P.G.E. for example

One other tip - if you have the email addresses for the contacts - separate out and filter on the domain.

re using a query to update values, I would caution its use, you may end updating to the wrong company. Although use within the context for a form - e.g. a list of 'unmatched' names in a multiselect listbox for example, select the names you want to make the same, enter the name in a textbox then run an update query via vba in code on a button for example.

For a validation rule, I would expect company names to be in a separate table so users can only select from this list using a combo. depending on the situation, new names might be verified against the sales ledger for example.

[b_rye_chan]

Thanks Ajax! The domain idea is fantastic, and the query via form suggestion gave me a few ideas that should make this much easier. +1