Could not delete error

[kilobyte]

I have created a query to delete duplicate records.

I followed the instructions on the following help site:

http://office.microsoft.com/en-us/assistance/HA010345581033.aspx#15

I created a join on the two tables just as required, on one of several fields that sometimes duplicates.

I have two criteria from the Distinct Records query that I joined the delete query with, which are a <>MaxOfOrgDate OR <>MaxOfHistID (the primary key).

The reason for that is I know that some of the OrgDates are duplicates, so if they are the same I just want to delete the lower HistID.

When I preview the query it all works perfectly and displays exactly what I want to delete, but when I try to run the query it says 'could not delete from specified tables.' The table is not read only so I don't know what the problem is.

Note: On this particular table, if one field is a duplicate, they all are, execpt the HistId field. This is because I append data into the table from another and then I want to delete duplicates, leaving only the data that has changed since I last appended.

Thanks for you help!

 

[Jon K]

I followed the instructions on the following help site:

http://office.microsoft.com/en-us/a...5581033.aspx#15

I created a join on the two tables just as required, ....

You can't use a JOIN in a Delete Query.

Instead you can use a Subselect (though a Subselect is inferior to a Join in terms of efficiency, especially when the table is large.)


The attached database contains the Employees table from the Northwind sample and a Delete Query. Unlike the example in the Microsoft page that uses the Distinct query as a Join, I used the Distinct query as a subselect in the Delete Query.

DELETE [EmployeeID]
FROM Employees
WHERE [LastName] & [FirstName] & [BirthDate] & " " & [HireDate] Not In
(Select LastName & FirstName & BirthDate & " " & Max(HireDate) from Employees group by LastName, FirstName, BirthDate);


You can run the query to delete Andrew Fuller's duplicate record from the table.
.