Duplicate Records Delete Query "Could Not Delete From Specified Tables"

[1cekream]

Hi All,

I am trying to run a delete query that removes duplicate records in a table. However, whenever I try to run the query I always get a "Could Not Delete From Specified Table" error.

Here is the SQL statement:

DELETE [UCS (Currently Orbiting Satellites)].*, [UCS (Currently Orbiting Satellites)].[COSPAR Number], [UCS (Currently Orbiting Satellites)].ID
FROM [UCS (Currently Orbiting Satellites)] INNER JOIN [Distinct] ON [UCS (Currently Orbiting Satellites)].[COSPAR Number] = Distinct.[COSPAR Number]
WHERE ((([UCS (Currently Orbiting Satellites)].[COSPAR Number])=[Distinct].[COSPAR Number]) AND (([UCS (Currently Orbiting Satellites)].ID)<>[Distinct].[MaxOfID]));

[UCS (Currently Orbiting Satellites)] - This is the table that contains the duplicate records
[Distinct] - This is the totals query

As it can be seen, I am using COSPAR Number as the field that determines which records are duplicates, and ID fields to help separate older and newer entries of the records (Since the ID field is an AutoNumber format). Can anyone see if there is something wrong with the SQL? Just to add, I also created a relationship between the COSPAR Number fields of both [UCS (Currently Orbiting Satellites)] table and [Distinct] query.

I would really appreciate it if anyone could offer a solution!

 

[spikepl]

First: Distinct is an SQL keyword, so I wonder if that mucks up your SQL. Change the name of that query into something else.

Then try changing DELETE into SELECT, and see if that works as desired

Finally, you should remove the specific fields, listed after [UCS (Currently Orbiting Satellites)].*

Also, you should not rely on the AutonumberID to indicate the sequence in terms of age. That can go wrong under some circumstances. Have a specific indicator like a date, or whatever

 

[1cekream]

Thanks for your time!

I tried all of the mentioned and still I get the error message:

1. Changed Distinct into another word

2. I ran the select query and everything was fine

3. I removed all fields right after [UCS (Currently Orbiting Satellites)].*

4. Unfortunately the ID number is the only value that is different in the comparison of duplicate records. I already changed my ID number type to 'Number' instead of 'Autonumber'

 

[spikepl]

I am not sure , so I hope somebody may have an idea.

What I would do try, while waiting for a better idea, is to have a temp table made by your totals query, and link with that instead. Your totals query is based on the same table, right? And logically speaking, you run a query that has to recalculate the totals and do something, and then recalculate the totals (and you have just changed the base) and do something, and that is a dog biting its own tail, so Access objects. But I could be wrong