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?
Comment
Watch Question

Top Expert 2016

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

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

Commented:
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

Author

Commented:
Works an abolsute treat - thank you very much

Commented:
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]