Self joined table in a delete query

[DW]

I am attempting to delete duplicate records of the same table, where they are
only different on one field, by making a duplicate of the table, and doing a
self join in a delete query. The query is unable to determine which copy of
the table to delete from. How do you specify which table to delete from
either in design view or in SQL?

 

[Tom van Stiphout]

On Wed, 24 Dec 2008 04:36:03 -0800, DW <[email protected]>
wrote:

I'm not entirely sure what you want to do, but to distinguish the two
tables in a self-join you give them both an alias:
select * from tblEmployees as Indians
inner join tblEmployees as Chiefs
on Chiefs.EmpID = Indians.ManagerID
(etc.)

-Tom.
Microsoft Access MVP

 

[John W. Vinson]

I am attempting to delete duplicate records of the same table, where they are
only different on one field, by making a duplicate of the table, and doing a
self join in a delete query. The query is unable to determine which copy of
the table to delete from. How do you specify which table to delete from
either in design view or in SQL?

By explicitly including the table name in the DELETE clause:

DELETE Copy1.*
FROM tablename As Copy1
INNER JOIN tablename As Copy2
ON ...

Note that a Totals query (with a Count) won't be updateable; perhaps you could
post your SQL.