Delete Punctuation in table data?? 3

[leicklda]

I am trying to merge 3 tables - each has different types of info on the same companies. Unfortunately, there is no universal customer # that I can merge these on, so I need to do it based on company name.

The problem is that the company names have slight differences. One table might say "A.O. Corporation", another might say "AO Corp", and another might say "A.O. Corp."

I'm lost as to what to do - there are thousands of companies. Is there a way I can analyze the table data and remove all punctuation and also remove key words such as "corp" and "inc"??

Thanks for any help.

 

[Golom]

The general mechanism for removing punctuation (or any character string) is

Field = Replace (Field, "RemoveThis", "" )

which will get rid of anything that you substitute for "RemoveThis".

You do need to be a bit careful however for reasons like the following

    Field = "The ABC Corporation"
    Field = Replace ( Field, "Corp", "" )   ' Produces "The ABC oration"

 

[Darrylles]

Hi leick,

This is the problem that un-normalised tables and multiple databases create - multiple records which hold the same 'information' but in different formats.

You are trying to address this problem.

You could try to produce queries or a small application to try to tackle this, but string manipulation is probably the most time-consuming task going.

Possibly the most efficient and quickest way to tackle this would be to produce an Access form that displays the company names from all of the 3 tables at once in 3 seperate list boxes.
You could use 'drag n drop' functionality to drag a correctly named company from one list and drop it onto the misnamed company in one of the other listboxes - code could then replace the incorrect company name with the correct one.

A query could then easily combine the company info from the 3 tables.

This IS a manual solution, but I will bet you that you will spend more time producing a solution than it would take to manually achieve the same result.

The real problem is, that code will only 'recognise' the similarities that you code into it - you will have to specify EVERY eventuality. (Hence you are pretty well doing it manually anyway).

Kind regards,

Darryle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[willir]

leicklda

Two more approachs to merging data...

The logic will be a little fuzzy becasue it will depend on your data.
- Have code that reads through one record table.
- Look for a match in the master table
- If it is missing, add it
- You can add a flag to the raw data table to flag a record if it has been used to write to the new master table.

Repeat for the second and third table.

How to look for a match -- try using the phone number, address -- this area will have to be tweaked.

Depending on your data and code, you should hit about 90 - 95 - 98% accuracy. The nice thing about using code is that you can tweak it and then re-run it with a minimal amount of work. The key is to find lookup fields to comapre between the databases.

Next...
In a table view or export to Excel or create a report or contiuous form to display all the names in sorted order. This will allow you to scan for duplicates.

After you have removed duplicates, you should then have a smaller list of customer codes to create.


I would be a little apprehensive of globally creating customer codes using the name of the business without punctuation. Golom, a very fast and bright star in this groups has given you the correct function to use. However..

- Customer code should be fairly short to allow for easy retrieval, say 6 to 12 characters. If you truncate at 6 to 12 characters, you may find yourself with assigning the same customer code to different businesses. If the customer code is too long, it may add frustration to data entry people.

- Company names softimes include articles such as "The Big Box Co". Here BIGBOX makes more sense than THEBIGBOX.


My take on this would be to use soemthing similar to the suggestion by Darryle.

- Create a form that queries your new master table.
- Add a command button that uses Golom's REPLACE command
- Scroll through your records
- If using the algorythm makes sense, click on the command button to automatically create the customer code.
- Otherwise, manually create it
- An error trap will allow you to determine if the customer code is used by another business.

You could actually create more than one command button - one uses formula A, the other formula B to create the code.

This way, most of the work is a couple of clicks with the mouse. But you will catch the exceptions that are required.

Richard