Searching/Updating multiple tables at once
I have encountered a database that has an occasional duplication because existing customers are occasionally entered as new. Sales and Service activity on the new record's related tables needs to be moved to the original main record and the new erroneous one deleted.
The customer records and data are related by a customer number field. Currently, the database operator is going to each of the underlying tables and manually changing the entry in the customer number field (the related field) to reflect the original number, then she deletes the new main record manually.
1. Is there a way of have the system recognize duplicates based on matching entries in multiple fields and, perhaps, stop entry? For example, could it look at First, Middle and Last names plus the Zip Code to eliminate duplication?
2. If that is too difficult, can I at least automate their current process, considering they have 5 underlying tables?
Thanks so much for any help!!