DetailPage-MSS-KB

Knowledge Base

Article ID: 889805 - Last Review: November 2, 2013 - Revision: 3.0

This article has been archived. It is offered "as is" and will no longer be updated.

INTRODUCTION

This article describes how to merge two records that were created for the same customer in Microsoft Business Solutions Retail Management System, Store Operations.

More information

  1. Back up the Store Operations database. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
    859729  (http://support.microsoft.com/kb/859729/ ) How to back up a database in Store Operations or Headquarters
  2. Start Store Operations Administrator, click Connect on the File menu, type the password, and then click OK.
  3. On the Query menu, click New.
  4. Copy and then paste the following SQL statements into the New Query window.

    Note Paste the SQL statements into the New Query window by pressing CTRL+V. You cannot right-click to paste into the New Query window.
    DECLARE @CustomerKeep INT
    
    DECLARE @CustomerDelete INT
    
    SET @CustomerKeep = (SELECT ID FROM Customer WHERE AccountNumber = 'X')
    
    SET @CustomerDelete = (SELECT ID FROM Customer WHERE AccountNumber = 'Y')
    
    UPDATE AccountReceivable
      SET CustomerID = @CustomerKeep
        WHERE CustomerID = @CustomerDelete
    
    UPDATE [Transaction]
      SET CustomerID = @CustomerKeep
        WHERE CustomerID = @CustomerDelete
    
    UPDATE Journal
      SET CustomerID = @CustomerKeep
        WHERE CustomerID = @CustomerDelete
    
    UPDATE [Order]
      SET CustomerID = @CustomerKeep
        WHERE CustomerID = @CustomerDelete 
    
    UPDATE Payment
      SET CustomerID = @CustomerKeep
        WHERE CustomerID = @CustomerDelete 
    
    UPDATE ShipTo
      SET CustomerID = @CustomerKeep
        WHERE CustomerID = @CustomerDelete
    
    UPDATE TransactionHold
      SET CustomerID = @CustomerKeep
        WHERE CustomerID = @CustomerDelete
  5. In the SQL statements in step 4, replace the X with the account number of the customer record that you want to keep. Replace the Y with the account number of the customer record that you want to remove.
  6. Press F5 to run the statements.
  7. On the Query menu, click New.
  8. Copy and then paste the following SQL statement into the New Query window.
    CREATE VIEW ARTemp AS SELECT CustomerID, SUM(Balance) AS Balance
      FROM AccountReceivable
        LEFT JOIN Customer ON AccountReceivable.CustomerID = Customer.ID 
          WHERE Customer.AccountNumber = 'X'
            GROUP BY CustomerID
  9. In the SQL statement in step 8, replace the X with the account number of the customer record that you want to keep.
  10. Press F5 to run the statement.
  11. On the Query menu, click New.
  12. Copy and then paste the following SQL statement into the New Query window.
    UPDATE Customer
      SET AccountBalance = ARTemp.Balance
        FROM ARTemp
          INNER JOIN customer ON ARTemp.CustomerID = Customer.ID
  13. Press the F5 key to run the statement.
  14. On the Query menu, click New.
  15. Copy and then paste the following SQL statement into the New Query window.
    Drop VIEW ARTemp
  16. Press F5 to run the statement.
  17. On the Query menu, click New.
  18. Copy and then paste the following SQL statements into the New Query window.
    DECLARE @CustomerKeep INT
     
    SET @CustomerKeep = (SELECT ID FROM Customer WHERE AccountNumber = 'X')
    
    
    UPDATE Customer SET Customer.TotalSales = A.TotalSales, Customer.TotalVisits = A.TotalVisits
      FROM Customer 
        LEFT JOIN (SELECT CustomerID, COUNT([Transaction].TransactionNumber) AS TotalVisits, SUM(Total) AS TotalSales
          FROM [Transaction] 
            GROUP BY CustomerID) A ON Customer.ID = A.CustomerID
              WHERE Customer.ID = @CustomerKeep
    
    UPDATE Customer
      SET Customer.TotalSavings = A.TotalSavings
        FROM Customer
          LEFT JOIN (SELECT CustomerID, SUM((FullPrice - Price)*Quantity) AS TotalSavings
            FROM TransactionEntry
              LEFT JOIN [Transaction] ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
                GROUP BY CustomerID) A ON Customer.ID = A.CustomerID
                  WHERE Customer.ID = @CustomerKeep
    
    UPDATE Customer
      SET Customer.LastVisit = [Transaction].Time
        FROM Customer
          LEFT JOIN [Transaction] ON Customer.ID = [Transaction].CustomerID
            WHERE [Transaction].Time IN (select max([Transaction].Time)
              FROM [Transaction]
                GROUP BY CustomerID) AND Customer.ID = @CustomerKeep
    
  19. In the SQL statements in step 18, replace the X with the account number of the customer record that you want to keep.
  20. Press F5 to run the statement.
  21. Quit Store Operations Administrator, and then click No when you are prompted to save the query to a file.
  22. Start Store Operations Manager.
  23. On the Database menu, click Customers, select the customer who you want to remove, and then click Delete.
  24. When you receive the following message, click Yes:
    The selected entry will be deleted. This operation can NOT be undone, do you wish to continue?

Applies to
  • Microsoft Retail Management System Store Operations 1.3
  • Microsoft Retail Management System Store Operations 1.2
Keywords: 
kbnosurvey kbarchive kbmbsmigrate kbhowto KB889805
Delen
Extra ondersteuningsopties
Microsoft Community Support-forums
Neem rechtstreeks contact met ons op
Een door Microsoft gecertificeerde partner zoeken
Microsoft Store