SQL -- how to use SQL statements to find non-existing records for an import job?

[Jessica H]

I need to import a list but I need to make sure not to import any records that email addresses are already existing in the current db. So I need to do some de-dup work before the import based on the email address field. I have two tables in MS Access -- tbl_existing (that has all existing records in the db) and tbl_Import (that is the list I need to import after the de-dup work). The return value should be:ID FirstName LastName Email_Add Tele_num

[Kenneth]

I'm not sure about MS Access, but in MySQL I would do something like this:SELECT i.ID, i.FirstName, i.LastName, i.Email_Add, i.Tele_numFROM tbl_import as i, tbl_existing as eWHERE i.email!=e.email

[Sunardy W]

EDIT :BlueFeather have better answer than mine :-)ORIGINAL :you want to insert some record from tbl_import into your tbl_existing where email address is not on your tbl_existing ?assuming your tbl_existing have same fieldname with tbl_import :Insert into tbl_existing select A.* from tbl_import as A where A.email_add not in (select C.email_add from tbl_existing as C)if you want to retrieve records from tbl_import where email_add not in tbl_existing :select A.ID, A.FirstName, A.LastName, A.Email_Add, A.Tele_Num from tbl_import as A where A.email_add not in (select C.email_add from tbl_existing as C)notes : not tested

[BlueFeather]

CONSIDER THIS:1) Part of what Sunardy told you is misleading. The field names (for email address) do NOT have to be the same name, but they DO have to be the SAME DATATYPE!2) IF the ID field contains unique values then Sunardy's query will NOT include records where the ID is different but the email address is the same. It is UNLIKELY that two people would have the same email address but NOT IMPOSSIBLE. For example, husband and wife could be using the same address OR two employees of the same company could conceivably share the same email if their tasks were identical but they worked different shifts. (Mary works 08:00-12:00 and Jane works 13:00-17:00)3) If you use the following query (which I have tested):INSERT INTO tbl_existing SELECT A.* FROM tbl_import AS A WHERE (((A.Email_Add) Not In (Select C.Email_Add From tbl_existing AS C))) OR (((A.ID) Not In (Select D.ID From tbl_existing AS D)));Then ......tbl_import can contain EVERTHING that is in tbl_existing PLUS additional records that are NOT in tbl_existing -- and the query will contain records where the ID is different OR the email address is different. That way you shouldn't have to do any pre-processing to eliminate duplicates.