João serras-pereira
asked on
ms/access deleting records based on a query
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?
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?
ASKER
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
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
ASKER
the select that comes out is:
DELETE t_9001_coreCount_tabMAV.co dMAVfull, A_0103_volta2_findDups_P03 .codMAV
FROM t_9001_coreCount_tabMAV INNER JOIN A_0103_volta2_findDups_P03 ON t_9001_coreCount_tabMAV.co dMAVfull = A_0103_volta2_findDups_P03 .codMAV;
DELETE t_9001_coreCount_tabMAV.co
FROM t_9001_coreCount_tabMAV INNER JOIN A_0103_volta2_findDups_P03
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.
ASKER
Ok. I am closing the question and reopen with a different title
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It works!!!! Thanks a lot!
Something like:
delete from myTable where myTableKey in (select some_col from your_query)