Duplicate Customers 1

[TrekBiker]

I have a situation with a Customer form where some customers have been entered twice or more. Their orders have then been spread over each version so making their Order Histories incorrect. I'd like to merge the sets of orders so they have the same CustomerID, then delete any other duplicate Customer records.

Renumbering CustomerIDs isn't possible so how can I get round this?

I'll add warnings in future to prevent this happening.

 

[Andrzejek]

How do you know that ABC Inc. and ABC Limited is the same Customer:

[pre]
CustID CustName
... ...
123 ABC Inc.
... ...
321 ABC Limited
... ...[/pre]

If that's the case, I would re-number all Orders with CustID of 321 to 123 in Orders table, and then [tt]Delete From Customers Where CustID = 321[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Thanks for this Andy. In case my original description of the problem wasn't clear these are added details.

Below is an example the same customer appearing twice in the Customers table, but with different CustomerIDs.

And this is from a subform showing the set of OrderIDs for the first of the customer's CustomerID. The Order Details appear in another table/subform.

The second set of OrderIDs is similar but with the second CustomerID.

I'd like to avoid changing OrderIDs because they are used in other documentation, and be able to move the second set of OrderIDs into the first, retaining their ID values. Then there'd be one complete set instead of two partial ones, so a single Order History for this customer. All of CustomerID, OrderID and OrderDetailsID are Autonumbers in their tables.

 

[Andrzejek]

I'd like to avoid changing OrderIDs" - I've never said to change OrderIDs.
The only change would be to CustomerID in Orders table. You do have CustomerID in Orders table, right?

If you want to leave CustomerID 17832943, and get rid of (duplicate) CustomerID 17831509:
First:[pre]
Update tblOrders
Set CustomerID = 17832943
Where CustomerID = 17831509
[/pre]
And then:[tt]
Delete From tblCustomers Where CustomerID = 17831509[/tt]
and JP's Kitchen is fixed.
--- Unless I am missing something here...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

That's great Andy, I've resolved it successfully as you suggested. A snag was that the CustomerID field in the Orders table was using a lookup so it displayed Company Name instead, and I was unable to do the fix. This database is riddled with this trick, which causes so many problems. By reverting to a Text field I was able to do as you said, then reintroduced the lookup because the consequences go far and wide.

Many thanks and as you hoped, JP is delighted.

 

[SkipVought]

Andy's a medium at large. JP?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

>JP?
JP's Kitchen, one of TrekBiker's customers...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson