Delete Query wont work

[duke_man]

I have a table with some duplicate records I want to delete automatically.
I found this on line help lin
http://office.microsoft.com/en-us/access/HA010345581033.aspx?pid=CH010713951033#13
and tried to follow the instructions. Worked OK until I got to the final
step of executing the delete query to remove the duplicate records. When I
try to execute the delete query I get a message saying "Could not delete from
specified tables".
I figured I must have messed up so I followed the example and steps provided
in the help page EXACTLY as written using the sample Northwind database.
Same problem.
The help page says "If you followed all the steps, and you have permissions
to delete records in the database, and the database is not locked or
read-only, the delete query will succeed in deleting the duplicate records."
I believe I meet all the criteria stated yet I am unable to execute the
delete query. Note that I am able to manually delete records from the table.

Any suggestions?

 

[Jeanette Cunningham]

duke_man,
delete queries can be tricky when you are learning how to do them.
The fact that you can open the table and delete the records, suggests that
the problem lies in how the delete query is constructed.
If you like to post details about the query, we might be able to help.
The first step is to construct a query or series of queries that will
identify exactly which records you want to delete.
You can use that column from that query as the criteria in the delete query.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[duke_man]

Thanks for reponding so quickly! After I posted this I continued to search
through the forum and found this suggestion from Michael Walsh: "Try adding
DISTINCTROW right after the DELETE and before the table name...". I did that
and eureka! It worked! The SQL for the delete query now looks like this:

DELETE DISTINCTROW Employees.*, Employees.LastName, Employees.FirstName,
Employees.BirthDate, Employees.HireDate
FROM Employees INNER JOIN [Employees - Distinct Records] AS [Distinct] ON
Employees.LastName = Distinct.LastName
WHERE (((Employees.LastName)=[Distinct].[LastName]) AND
((Employees.FirstName)=[Distinct].[FirstName]) AND
((Employees.BirthDate)=[Distinct].[BirthDate]) AND
((Employees.HireDate)<>[Distinct].[MinOfHireDate]));

Adding DISTINCTROW did the trick. Maybe I should tell Microsoft?

 

[Jeanette Cunningham]

Glad you got it working

Jeanette Cunningham -- Melbourne Victoria Australia

Thanks for reponding so quickly! After I posted this I continued to
search
through the forum and found this suggestion from Michael Walsh: "Try
adding
DISTINCTROW right after the DELETE and before the table name...". I did
that
and eureka! It worked! The SQL for the delete query now looks like this:

DELETE DISTINCTROW Employees.*, Employees.LastName, Employees.FirstName,
Employees.BirthDate, Employees.HireDate
FROM Employees INNER JOIN [Employees - Distinct Records] AS [Distinct] ON
Employees.LastName = Distinct.LastName
WHERE (((Employees.LastName)=[Distinct].[LastName]) AND
((Employees.FirstName)=[Distinct].[FirstName]) AND
((Employees.BirthDate)=[Distinct].[BirthDate]) AND
((Employees.HireDate)<>[Distinct].[MinOfHireDate]));

Adding DISTINCTROW did the trick. Maybe I should tell Microsoft?

duke_man,
delete queries can be tricky when you are learning how to do them.
The fact that you can open the table and delete the records, suggests
that
the problem lies in how the delete query is constructed.
If you like to post details about the query, we might be able to help.
The first step is to construct a query or series of queries that will
identify exactly which records you want to delete.
You can use that column from that query as the criteria in the delete
query.


Jeanette Cunningham -- Melbourne Victoria Australia