Link to home
Start Free TrialLog in
Avatar of João serras-pereira
João serras-pereiraFlag 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?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Something like:

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

Avatar of 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
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;

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

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
Avatar of Mark Edwards
Mark Edwards
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It works!!!! Thanks a lot!