Form to consolidate duplicate customers?

[Garindan]

Hi all, could somebody please help me to construct this form?

I have a few duplicate customer queries (different duplicate fields etc). I would like to construct a form to make it easy for an end user to remove the duplicates.

The main problems are...
1. the newer customer is to be deleted
2. any/all orders from the newer customer will be moved to the older customer before deletion.
3. preferably an easy way to switch between the duplicate queries in the same form, though if this is a problem I could just have one small dialog box/form, linking to 3 different duplicate query/forms.

I think other changes between the customers will be easy enough to just do in the subform, i.e update email address/tel/status/salesperson etc. These can just be edited directly?

But the transferring of orders can't be edited directly because there may be several orders and could be errors etc.

tblCustomers [CustomerNumber] primary key

tblCustomerOrders [OrderNumber] primary key
[CustomerNumber] linking to tblCustomers

So all that needs to be updated is [CustomerNumber] in tblCustomerOrders. All orders for the newer customer to have the CustomerNumber field updated to match the older customer. Then the newer customer is deleted.

I would just have a button with a message box to run the code, update CustomerNumber/s and delete record.

Could anybody help me with this? Many many thanks in advance!

 

[jdraw]

I really have to ask why do you have duplicate Customers?
That is a symptom of a design or processing issue in most cases. Please tell us more about how these duplicates entered your database, and what do they represent.

Please tell us what makes these Customers duplicates.

 

[Garindan]

I really have to ask why do you have duplicate Customers? Please tell us what makes these Customers duplicates.

Thanks for the reply. Unfortunately the duplicates is a request from the business owner...

He wants a version of the database for a salesperson. The salesperson cannot see existing customers (at the owners request). The salesperson has to add his 'new' customers at the end of each day/week.

The duplicates form is to look for any duplicate entries once a week or so and clean them up.

I can't see any better way of doing this? It's the same (or easier) than giving the salesperson a blank database and then updating the existing database.

Hope that helps?

EDIT: Forgot to mention, there's no duplicates at the moment. I'm trying to clear up any new ones.