none
Find duplicates across 2 columns & in same columns

    Question

  • Hi All.

    A challenging one for me but I'm sure not for you. In Main_Table there are 2 fields for email addresses, Personal & Business. I need to find any duplicates within Personal & within Business as well as across the columns. ie If there is a match in Personal or a match in Business I want it listed along with First_Name & Surname.

    I did find some code that will give me a count of duplicates in a similar scenario, but I need to list each record for follow up. Anyone got any ideas on how best I can achieve this?

    Monday, September 19, 2011 10:19 PM

Answers

  • You have to bring the two fields into one, even if it is "virtually", trough a query:

     

    SELECT clientID, homeAddress AS address FROM tableName WHERE homeAddress  IS NOT NULL
    UNION ALL
    SELECT clientID, businesAddress FROM tableName WHERE businessAddress IS NOT NULL

     

     

    Save that query, say, as q1, then

     

    SELECT clientID, address
    FROM q1
    GROUP BY  clientID, address
    HAVING COUNT(*) >1

     

    will return duplicated couples.

     

     

    Monday, September 19, 2011 10:37 PM
    Moderator

All replies

  • Try this --

    SELECT Main_Table.Personal, Main_Table.Business, Main_Table.First_Name, Main_Table.Surname
    FROM Main_Table INNER JOIN Main_Table AS Main_Table_1 ON (Main_Table.Surname = Main_Table_1.Surname) AND (Main_Table.First_Name = Main_Table_1.First_Name)
    WHERE (((Main_Table.Business)=[Main_Table].[Personal])) OR (((Main_Table.Personal)=[Main_Table_1].[Business]))
    GROUP BY Main_Table.Personal, Main_Table.Business, Main_Table.First_Name, Main_Table.Surname;

    Monday, September 19, 2011 10:37 PM
  • You have to bring the two fields into one, even if it is "virtually", trough a query:

     

    SELECT clientID, homeAddress AS address FROM tableName WHERE homeAddress  IS NOT NULL
    UNION ALL
    SELECT clientID, businesAddress FROM tableName WHERE businessAddress IS NOT NULL

     

     

    Save that query, say, as q1, then

     

    SELECT clientID, address
    FROM q1
    GROUP BY  clientID, address
    HAVING COUNT(*) >1

     

    will return duplicated couples.

     

     

    Monday, September 19, 2011 10:37 PM
    Moderator
  • Hi Karl,

    Thanks for the speedy response. My problem with this solution is that it requires the First_Name & Surname to also be exact duplicates which is not the case. The one item people seem to be careful with is email addresses & if I run a duplicates query just on one column comparing email addresses, I get quite a few duplicates.

    Monday, September 19, 2011 10:57 PM
  • Hi Vanderghast,

    I had thought along those lines but kept trying to complicate it when it really was quite simple. Merge the columns into one table / query then run a find duplicates query on that data. Because of the union query the find duplicates query takes some time to run even though there is only a total of 8700 records when merged.

    But it works! Thank you for the help.

     

    Monday, September 19, 2011 11:26 PM