How to create form/code for viewing and consolidating duplicate customers?

[Garindan]

Hi all, this one has me stumped.

I have a database with customers/orders etc. I need to create a form or something which will show if a duplicate customer is entered. I know about the duplicate records query wizard, but I also need an easy way to consolidate and delete records. When a duplicate customer is found, move the customers order/s [OrderNumber] to the existing customer and delete the new/latest customer record.

Customers are in tblCustomers, their orders are in tblCustomerOrders, [CustomerNumber] is the joining field.

I was thinking of just a date/time field for [DateCustomerEntered] with default value Now to keep track of the later customers?

 

[Garindan]

I think firstly with the duplicates query, I have one which simply looks for duplicate surnames [Surname], with the same street and house numbers [Street] [House_FlatNumber].

How can I make it more complex to ensure I catch them all? i.e. same business name [BusinessName], wrong spelling of surname or street, etc etc.

 

[spikepl]

If this isa one-off, clean up your data and be done with it. If not, then what about preventing duplicates at entry rather than doing a clean up? How do you know if a customer is a new customer vs. a duplicate?

UPDATE:

Why is it important to consolidate the data?

 

[Garindan]

Thanks for the reply! Unfortunately it's not a one off.

The problem is that the owner wants a section for a salesperson, to add new customers. He wants to hide existing customers from the salesperson. In which case it seems like theres no option to prevent duplicates at entry?

 

[Garindan]

UPDATE:

Why is it important to consolidate the data?

The owner pays a salesperson more for selling to a new customer, less for selling to existing customers. If the data is not consolidated then all the salespersons entries will be marked as 'new customers' and pay more.

I know I know it's complicated but thats how he wants it.

So it has to be that a salesperson can enter all his sales/customers to the database, but if one of them turns out to be an existing customer it has to be known so it will pay less.... :banghead:

 

[spikepl]

email or phone number may be more reliable (=readable/discernable by a stupid machine )than address or name

 

[Garindan]

Ok thanks.

So is it possible to consolidate data in a form with a duplicate query base?

 

[Garindan]

Ok I know more of whats needed now so I'll start a thread in the forms section. Thanks.