We help IT Professionals succeed at work.

ms/access deleting records based on a query

João serras-pereira
João serras-pereira asked
on
Hi. I have an ms/access database that has a table from which I need to delete records according to specific criteria.

This is [myTable] with a key, [myTableKey]
 I have a set of analysis, running from other tables that yield keys [myTest1Key], [myTest2Key], ... [myTestNKey]. I need to delete the records from myTable  where [myTableKey matches the [myTestiKey], so the [myTable] is totally cleaned out.
How do I do it?
Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:

Something like:

delete from   myTable where   myTableKey in (select some_col from your_query)

Ho

I am a REAL rookie on selects. I am trying to use the graphical interface. Please check attached. On the left there is the real table from which I want to delete the records. On th right, the records to be deleted. The tables are properly linked and when viewing the select in datasheet mode,, it is perfect (screenshot attached as well). But when I ran, I am getting the  error "specify the table where containing the records you want to delete). Where can I specify them?
Screenshot-2020-01-30-at-18.02.07.png
Screenshot-2020-01-30-at-18.05.32.png
the select that comes out is:

DELETE t_9001_coreCount_tabMAV.codMAVfull, A_0103_volta2_findDups_P03.codMAV
FROM t_9001_coreCount_tabMAV INNER JOIN A_0103_volta2_findDups_P03 ON t_9001_coreCount_tabMAV.codMAVfull = A_0103_volta2_findDups_P03.codMAV;
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:

I'm not a GUI person.  Too many years on the command line...


Right click and go to SQL view.  Try the delete syntax I provided.  If it works, then to to designer and see what designer shows it like.

Ok. I am closing the question and reopen with a different title
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:

No need to close and re-open.  You can click the help bell to pull in other Experts.  I've added the Microsoft Access Topic Area.


As long as the delete works, not sure why you have to do it through the GUI.

Chief Technology Officer
Commented:
In Access, you can't run a DELETE query with linked tables/queries (but you CAN in SQL Server!).
I recently created a sample database to show another rookie how to setup a delete query using the IN() clause as slightwv recommended.

See if the attached demo sheds some light on the matter.  
The demo uses a concatenation of 2 fields as a record id field, but you can adapt it to just use your one field.
It also shows how to use a query that has the records you want to KEEP and delete all the others.

Let us know if you need more help...
Keep-ID-and-MaxDate.accdb
It works!!!! Thanks a lot!