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?
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?
Rey Obrero (Capricorn1)
make the field "Company GUID" a primary key, with no duplicates.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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]
INSERT INTO [Permanent Companies] ([Company GUID], [Company Name]) SELECT DISTINCT [Permanent Bookings].[Company GUID], [Permanent Bookings].[Company Name] FROM [Permanent Bookings]