Microsoft Dynamics RMS (Archived)

One of the Store has duplicate customers that have same account#

(0) Share

Report

Posted on by Community Member Microsoft Employee

This is very weird, you an Account number is unique but one of the store has same account number of customers.

I think I have more than a thousands of customers they have same duplicate customers.

But from the HQ will only have one customer. So I am trying delete the duplicate customer but there are so many.

I can't delete customers from the SO Manager, because they are global customer.

http://i29.photobucket.com/albums/c259/xkrntamax/Duplicate_Customer.jpg

When I run the query and only the difference is ID numbers are different. HQID is same, account numbers are same.

How can I delete this duplicate customers? And it is very ridiculous that some of the customer have info like address or number but some of the duplicate customer does not have info.

Can anyone write me a SQL query? I made a backup and trying to see how this thing works...

Thanks in advance.

*This post is locked for comments

All responses (8)

Answers (0)

Suggested answer

Alwani 710 on at

Like (0)

Report

Re: One of the Store has duplicate customers that have same account#

Tell me if you need still help.
Alwani 710 on at

Like (0)

Report

Re: One of the Store has duplicate customers that have same account#

I6866.Import2.JPG"/resized-image.ashx/__size/550x0/__key/CommunityServer-Discussions-Components-Files/106/0250.Import.JPG" border="0" />

1. Select Excel as your source and then in browse select the Excel File you Just created.

2. Select the Destination as your RMS Database > Press Next

3. Select Copy data from one or more tables or view

4. Select now your Table with checkbox (remember normally if you named your excel file it will come with name plus $ i.e. Shee1$

5. Once you Select the Excel File in Destination and select the Customer table (make sure that the fields names also come when you exported the data to excel file)

6. Edit the Mapping and make sure it mapped with correct fields and then click next> then next and click Finish to upload the data.

If you face the problem I am on Skype to help you out in this.
Community Member Microsoft Employee on at

Like (0)

Report

Re: One of the Store has duplicate customers that have same account#

I added you on Skype. My ID is danny.ryu

So I ran the Select query and export all the results and I delete duplicate customers using excel.

Afterthat, I ran the Delete Query after that, how do I upload the customer?
Alwani 710 on at

Like (0)

Report

Re: One of the Store has duplicate customers that have same account#

hi Ryu,

I hope you not run this query on production, else it is the wrong way. I am on Skype my id is akberalwani.

The first query will tell you duplicate customer but also tell you those customer who have no transaction in [Transaction] and TransactionHold table.

Second query will delete the all duplicate customer. My suggestion take the back, run select query and then export the data to Excel, Run remove Duplicate from Excel and then load the single customer to customer table.

Let me know if you need help.
Community Member Microsoft Employee on at

Like (0)

Report

Re: One of the Store has duplicate customers that have same account#

Thanks for the input!

I ran the query and it only shows me all the duplicate customers.

and when I run the delete query, it deletes the whole customers only when they are duplicated.

Duplicate customers are 92314, so the half is 46157 customers are duplicated. but the query you gave me it deletes the whole entire duplicate customers. now i only have 843 custmers that were not duplicated at all. And suggestion?
Suggested answer

Alwani 710 on at

Like (0)

Report

Re: One of the Store has duplicate customers that have same account#

now it time to remove those which are duplicated, if you want I can connect team viewer to help you resolve this issue. However you can run below query to get those customer who are duplicated also they have no transaction

select * from Customer

Where AccountNumber in (

select AccountNumber

from customer

group by AccountNumber

having count(AccountNumber)>1)

and (ID not in (Select CustomerID from [Transaction])

OR ID not in (Select CustomerID from TransactionHold))

Now if you really want to delete then you can apply similar query as:

delete Customer

Where AccountNumber in (

select AccountNumber

from customer

group by AccountNumber

having count(AccountNumber)>1)

and (ID not in (Select CustomerID from [Transaction])

OR ID not in (Select CustomerID from TransactionHold))

I can be contacted on Skype to help you out . akberalwani
Community Member Microsoft Employee on at

Like (0)

Report

Re: One of the Store has duplicate customers that have same account#

I have all the customers show up for the result and every single customers are Global Customer.

Which is, All the duplicate customers are Global Customers.

any recommendation?
Alwani 710 on at

Like (0)

Report

Re: One of the Store has duplicate customers that have same account#

The key field in Customer table is not the Account Number as per your .jpg. it is the ID field, so duplication cause can be many cases, may me some of these account are not global, first identify this with below SQL Query.

SELECT * FROM CUSTOMER

WHERE AccountNumber in (

select AccountNumber

From Customer

group by AccountNumber

having Count(*)>1)

Go to SO > Administrator program and Click on New Query and run this. First get the result and see which customer is global and which one is not, check the data opened the account etc.