Delete not working from table of objIDs (1 Viewer)

[Access_guy49]

This should be so simple, i don't get it.
I have a table of object ID's
I want to delete the records in a much larger table. but i only want to delete the records found in my To Delete table...
When i add both tables into the query design, link them, then try to delete from the master table, i get "can't delete from specified table"
I have no idea what i've done wrong!

 

[boblarson]

What is the SQL you are trying to use? All you need to do is join the two tables on the ID field and then do the delete. So, if you want to delete all records that exist in the other table you would just join that table to the one you want to delete from and then use the asterisk. (let's use an example - let's say we have a copy of the Employees table from Northwind - say Employees2. And I make another copy of the table - Employees3. Then I want to delete from Employees3 all odd ID numbers. So in my Employees2 table, let's say that I have just the odd numbers. I can then join on EmployeeID and run the delete query which is:

DELETE Employees3.*
FROM Employees3 INNER JOIN Employees2 ON Employees3.EmployeeID = Employees2.EmployeeID;

 

[Access_guy49]

So just to be clear on what i did.
I ran a find duplicate query, then made a create table query that takes those duplicates and gets the first object id for every duplicate. So now i have a table of object ID's that i want to delete from Parc_data.

The SQL:

DELETE Parc_Data.*
FROM ObjectIDtodelete INNER JOIN Parc_Data ON ObjectIDtodelete.FirstOfOBJECTID = Parc_Data.OBJECTID;

I have attached an image of the design view and the error that i get.

Thanks for the response Bob

 

[boblarson]

So, it would appear that you have a little bit of a problem. If you are trying to delete duplicates of the same ID then it won't work like this. You should have a primary key which you can link on in order to narrow it down to a single record. So you may need to use VBA to iterate through to do this, or someone else might be able to suggest a subquery that might do the trick.

 

[Access_guy49]

Bob, man i never thought of that!!!!
I just checked it and sure enough, the objectID's are duplicated!!!!
This is the worst.
So to add to that problem, i can't add a new autoID field because apparently you can only have one autoID field in a table. And when i try to change my current auto ID field in my master table it bugs out saying that i don't have enough memory or disk space...
i have 500GB free space!!! and my machine has 8gigs of ram!!!! it didn't go above 20% usage on my memory!!!
Worst data ever!