Background

Ecometry is the order managment system we use at Velowear.com (one of my responsibilities as Voler's IT Manager).  Ecometry has many features for locating duplicates; but it does not include the ability to search through the entire customer data-set to find possible duplicates.  Given this limitation I was assigned the task to find/merge duplicates using a third party tool (I was working for Velowear as a consultant at this time).

Details

I could not locate a third party tool so I decided to create my own system.  Step one was to generate a view in SQL Server that would perform the heavy data crunching.  Access than looks at this view and uses VBA to find possible matches.  The Access database then presents these possible matches to the user who can override them as they wish.  The final product is a merge file that Ecometry uses to actually merge the customer records.  The Access database doesn't actually modify the Ecometry database at all, it just looks at it and then generate the merge file.

Instructions

Note: these instructions are intended to be used for someone that is knowledgeable with SQL Server and Access. 

This process should work with Oracle as well, but slight modifications may be required to get it to work.  This "software" is provided w/o any warranty expressed or implied and should be used at your own/company's risk.  Let me know if I missed a step in the instructions below, or if I need to make things clearer...

  1. Download zip file containing Access database and SQL for creating database Views.
  2. Create the following three views in your Ecometry database: EmailAddressView, CustomerEmailListngView, DuplicateCustFinderView (SQL attached in zip file)
  3. Copy the Access database to the location you want to run it from (we have it on network drive we run Ecometry from, but it can be on any machine that has network access to the database and the Ecometry Pub folder.
  4. Change the default field values for DB, UID, PW, and pub location (in Access open form in design mode and change default value to what it should be).  image
  5. To actually run the merge follow steps 1,2,3.  Play around with it a little but using your test database, but don't accidentally put your test merge file into your live database pub folder...

Note: If your database server is under load, or you customer data-set is too large to handle you may get a timeout during step 1.  To solve you need to increase the timeout settings, or look at optimizing the provided SQL queries as these are what are taking too long to execute.