Hi,

I having trouble getting a delete query to work. I'm trying to delete my duplicate entries. My query returns the correct data to delete when I view it in datasheet view. However, when I run the query I get the dreaded "Could not delete from specified tables." Unique records in properties is set to yes, just like it should be. Can't figure out why the query won't run.

Here's the SQL language that Access created:
DELETE DISTINCTROW [Main Table].*, [Main Table].County, [Main Table].[Beg Book Number], [Main Table].Suffix, [Main Table].[Received Date]
FROM [Main Table], Duplicates_not_Shown AS 1
WHERE ((([Main Table].County)=[1].[county]) AND (([Main Table].[Beg Book Number])=[1].[beg book number]) AND (([Main Table].Suffix)=[1].[suffix]) AND (([Main Table].[Received Date])<>[1].[maxofreceived date]));

Any help would be great.

Thanks,
K-C

There are several problems...

One is that you are deleting more than "*" in your field list. Another is that you can't use DISTINCTROW in the DELETE statement. And finally, you are not joining to Duplicates_not_shown, so the delete is trying to delete from both [Main Table] and Duplicates_not_shown.

If you're using the query builder, you can get rid of the extra fields by removing the check from the "Show:" row of the superfluous fields. But that won't solve the problem.

I think what you'll have to do is use a SELECT query that will make a list of IDs that you want to keep in your [Main Table]. Then create another query that has a delete statement like this..

DELETE * From [Main Table] WHERE ID Not In (Select ID From [the query that has the IDs you want to keep])

For example, if Duplicates_not_Shown is actually a list of the [Main Table] records you want to keep, just change that query to select only the ID feild. Then the delete query is...

DELETE * From [Main Table] WHERE ID Not In (Select ID from Duplicates_not_Shown)

Hopefully you do have an ID field (primary key) defined in [Main Table] that is unique for each record in [Main Table]. If not, you have a tough row to hoe. But you can still do it by Joining [Main Table] with a list of the [Main Table] records that you DO want to delete.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org