Delete query - cannot delete from specified tables

[Guest]

I followed the advice in Microsoft Office Online for deleting duplicate
records by having one table with all records and another (a query) with
distinct values for fields which may be duplicated and a further field for
the field upon which I decide which record to delete. On Microsoft Office
Online I was advised to join my original table and my distinct view on the
first duplicatable field so that I would not get the message "Cannot delete
from specified tables". Unfortunately I do get this message when I try to run
my delete query even though when I look at my query in datasheet view to see
which records would be deleted I get exactly the right result.
Can anybody shed some light on this?

 

[Wolfgang Kais]

Hello "aristaeus".

I followed the advice in Microsoft Office Online for deleting duplicate
records by having one table with all records and another (a query)
with distinct values for fields which may be duplicated and a further
field for the field upon which I decide which record to delete.

I suggest not to join anything in your query, instead use a subquery.

On Microsoft Office Online I was advised to join my original table
and my distinct view on the first duplicatable field so that I would
not get the message "Cannot delete from specified tables".

Does your "join query" has the "hide duplicates" property set to true?
Does the table have a primary key?
Could you have told us the link to that page?

Unfortunately I do get this message when I try to run my delete query
even though when I look at my query in datasheet view to see which
records would be deleted I get exactly the right result.

And can you delet a record in that datasheet view?

Since you did neither supply the table definition nor your query,
here's a very general solution that keeps the minimum PK:
Delete From TableName Where Exists
(Select * From TableName As tmp Where
(tmp.DupField1 = TableName.DupField1) And
(tmp.DupField2 = TableName.DupField2) And ...
...And (tmp.PK < TableName.PK))

 

[Guest]

Thanks Wolfgang:

I did find the answer eventually - I opened the query in SQL view and simply
put DISTINCTROW directly after DELETE.

The query read:

DELETE DISTINCTROW Models4.*, Models4.Name, Models4.Address1,
Models4.Generation
FROM Models4 INNER JOIN DistinctModels AS [Distinct] ON
Models4.Name=Distinct.Name
WHERE (((Models4.Name)=distinct.name) And
((Models4.Address1)=distinct.address1) And
((Models4.Generation)<>distinct.minofgeneration));