We help IT Professionals succeed at work.

Show Single Duplicate

Dear Experts

I have an Access 2010 database which has a table called Permanent Bookings.  This table contain data regarding companies and contacts at companies.  This table is populated via an “append data import” from excel. This produces records like this:

Contact name > contact jobtitle> company name> Company GUID > Company Address
Joe Blogs > team head > Microsoft > 1234gqrdwer23 > USA
Fred Smith > team player > Microsoft > 1234gqrdwer23 > USA
David White  >tea boy > Microsoft > 1234gqrdwer23 > USA

What I am trying to do is append just 1 copy of the company information into another table (Permanent Companies) i.e. it will only import 1 copy of

company name> Company GUID > Company Address
Microsoft > 1234gqrdwer23 > USA

The field that defines the duplicate is Company GUID

Can anybody help?
Watch Question

Top Expert 2016

make the field "Company GUID" a primary key, with no duplicates.

Top Expert 2016
or create an index on field "Company GUID" and set the  unique value to Yes
Top Expert 2016

another way is to dump the imported records from excel into  a temp table, then run this append query

insert into [Permanent Bookings]
select tmpTable.* from tempTable
where exists(select [Permanent Bookings].[Company GUID] from [Permanent Bookings] where [Permanent Bookings].[Company GUID] =[tmpTable].[Company GUID])=false


Works an abolsute treat - thank you very much

I think what you want is:

INSERT INTO [Permanent Companies] ([Company GUID], [Company Name]) SELECT DISTINCT [Permanent Bookings].[Company GUID], [Permanent Bookings].[Company Name] FROM [Permanent Bookings]