Trying to delete duplicate records

[Rick]

I am going nuts on this and hope someone can help. I have
a query, or I should say am trying to WRITE a query where
I find duplicate records between a master and a secondary
table. These tables are identical, same fields one is
meant to be appended to the other after it is de-duped.
What I am doing is importing a text file into a "temporary
work table" that is identical to my "Master" table. I
want to clear the dups out of the temporary table then
append all the non dups to the master table.

What I have done so far: Created an append query that
copies all dups to a SECOND work table (works)
above append query and simply modified it to make it
a "Delete" query.

In doing this I get the following error "Specify the table
containing the records you want to delete"

I look at the SQL, all looks good (to me, not an expert
with delete queries).

My query looks like this:
DELETE DISTINCTROW [Temp].[fieldname]
FROM MASTER INNER JOIN [Temp] ON MASTER.fieldname = [Temp].
[fieldname]
WHERE ((([Temp].[fieldname])=[fieldname]));

As a SELECT or a APPEND query, it works.

Thanks in advance!

In design view both tables are present.

 

[Michel Walsh]

HI,

It seems it is a syntax problem:

DELETE DISTINCTROW WhichTableToDeleteFrom.*
FROM ... SOME JOIN ...

You didn't use *, you use a field name. Since there is a join, the table
submitted to the delete has to be explicitly mentioned.


Hoping it may help,
Vanderghast, Access MVP