Avatar of João serras-pereira
João serras-pereira
Flag for Portugal 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?
DatabasesMicrosoft Access

Avatar of undefined
Last Comment
João serras-pereira

8/22/2022 - Mon
slightwv (䄆 Netminder)

Something like:

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

João serras-pereira

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
João serras-pereira

ASKER
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;
Your help has saved me hundreds of hours of internet surfing.
fblack61
slightwv (䄆 Netminder)

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.

João serras-pereira

ASKER
Ok. I am closing the question and reopen with a different title
slightwv (䄆 Netminder)

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.

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Mark Edwards

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
João serras-pereira

ASKER
It works!!!! Thanks a lot!