asked on

Delete duplicate data from access tables while creating new table

Hello

I have 2 tables in Access that I need to create a new table with. I want the new table to not contain any data from table 2 (duplicate data).

So table1 will be my starting table and I need to delete any duplicate CustID from it that can be found in table2.

So New_Table will only have records in it that was not duplicated.

I hope I am not making this confusing but table1 has updated data in it. And Table2 has old data in it. So I want to create New_Table that basically only has the new custID data that was not found in Table2. (CustID is the only field I am looking at for duplicate. So if The custID has been used in table2 I don’t want any of those records to show up in New_Table)

I have attached a access database that shows what I am talking about. Table1, Table2 and need to know how to create New_Table

Thanks for your help

FaithDan
startingpoint1.accdb

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PrasanthKrishnan

Try this query

INSERT INTO NEW_TABLE SELECT a.CUSTID,a.NAME1,a.PHONE, a.ITEMID FROM
(
SELECT custid, name1, phone, itemid from table1
union
SELECT custid, name1, phone, itemid from table2
) a

FaithDan

ASKER

Thank you again... That's exactly what I was needing.

Thank you very much

FaithDan