Search for duplicates

[shaunacol]

I import a list of new enquiries into a new table (Copy_of_table_client) on a daily basis. These enquiries come from different sources so it is possible that they contain duplicate leads. In addition before I add them into Tbl_client to be given to the salespeople I need to check that they are not a duplicate of a prrevious enquiry/lead that is already in Tbl_client from another day. So I need to:


1. Check for any duplicates in my import (Copy_of_table_cleint) and remove them
2. Check for any records that exist in both tables (Tbl_Client and copy_of_table_client) and remove them from Tbl_copy_of_table_client)
3. Import any existing records from copy_of_table_client into tbl_client then delete copy_of_table_client table ready for the new import the next day

The fields in Tbl_copy_of_client include FirstName, Surname, Email, Telephone, Notes. These fields all exist in Tbl_Client too. My thinking is that the most reliable and unique value for comparision maybe the Email.

 

[PHV]

What is the PrimaryKey of Tbl_Client and copy_of_table_client ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shaunacol]

For Tbl_Client the PK is Client_ID. At the moment the imported table Copy_of_table_client is just a list of names and contact details and does not have a primary key but it would be easy to add one. Thanks

 

[PHV]

it would be easy to add one
So, say you'll name it ID

DELETE * FROM copy_of_table_client
WHERE ID Not In (SELECT Max(ID) FROM copy_of_table_client GROUP BY Email)

DELETE T.*
FROM copy_of_table_client T INNER JOIN Tbl_Client C ON T.Email = C.Email

DELETE * FROM copy_of_table_client
WHERE Email In (SELECT Email FROM Tbl_Client)

INSERT INTO Tbl_Client (FirstName, Surname, Email, Telephone, Notes)
SELECT FirstName, Surname, Email, Telephone, Notes FROM copy_of_table_client

DELETE * FROM copy_of_table_client

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shaunacol]

That looks great, and I even think I follow it! I will test this weekend, many thanks for your help