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