Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-19-2019, 12:55 AM   #1
zahranahmad70
Newly Registered User
 
Join Date: Oct 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
zahranahmad70 is on a distinguished road
Delete Records with conditions

i have 2 tables, i need to delete all records from one table where matching records of second table. please help

Table one Fields (Employee_Master): Emp_no, Certificate_issue_Date
Table two Fields (Employee_Certificates): Emp_no, Certificate_issue_Date

i wand to delete all records from table two with matching records of table one,
but i keep receiving "Could not delete from specific tables"

Please help

zahranahmad70 is offline   Reply With Quote
Old 10-19-2019, 01:09 AM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Delete Records with conditions

DELETE * FROM employee_certificates WHERE emp_no IN (SELECT emp_no FROM employee_master);
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
faap (10-19-2019)
Old 10-19-2019, 01:11 AM   #3
zahranahmad70
Newly Registered User
 
Join Date: Oct 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
zahranahmad70 is on a distinguished road
Re: Delete Records with conditions

Thank you arnelgp. the condition is emp_no and certificate issue date, they both should mach

zahranahmad70 is offline   Reply With Quote
Old 10-19-2019, 01:23 AM   #4
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Delete Records with conditions

From which table are you deleting?
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-19-2019, 01:34 AM   #5
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Delete Records with conditions

DELETE * FROM employee_certificates WHERE emp_no & certificate_issue_date IN
(SELECT emp_no & certificate_issue_date FROM employee_master);
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
faap (10-19-2019)
Old 10-19-2019, 07:33 AM   #6
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,505
Thanks: 58
Thanked 1,420 Times in 1,401 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Delete Records with conditions

Quote:
Originally Posted by zahranahmad70 View Post
i have 2 tables, i need to delete all records from one table where matching records of second table. please help

Table one Fields (Employee_Master): Emp_no, Certificate_issue_Date
Table two Fields (Employee_Certificates): Emp_no, Certificate_issue_Date

i wand to delete all records from table two with matching records of table one,
but i keep receiving "Could not delete from specific tables"

Please help
Hi. Welcome to AWF!

This may or may not work but maybe also try something like:

Code:
DELETE Table1.*
FROM Table1
INNER JOIN Table2
ON Table1.Emp_no=Table2.Emp_no
  AND Table1.Certificate_issue_Date=Table2.Certificate_issue_Date
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 10-19-2019, 08:06 AM   #7
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,271
Thanks: 115
Thanked 3,081 Times in 2,799 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Delete Records with conditions

You may get an error message that you cannot delete from the specified table.
If so, set Unique Records =Yes in the query properties.
That's the same as using DELETE DISTINCTROW … in your SQL statement

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 10-20-2019, 09:16 PM   #8
zahranahmad70
Newly Registered User
 
Join Date: Oct 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
zahranahmad70 is on a distinguished road
Re: Delete Records with conditions

Thank you isladogs.. (Unique Record = yes) did the trick, it worked like magic

zahranahmad70 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Attempting to delete a record without Cascade Delete Records checked GoodWrench Modules & VBA 2 08-19-2016 01:58 PM
Question How do I update and delete using conditions Smyalee General 1 05-31-2015 05:56 AM
Delete query - delete records based on criteria in another table foxtrot123 Queries 8 06-04-2013 12:36 PM
Counting Records that meet conditions matt330 Reports 1 03-10-2006 05:55 AM
Preventing new records on conditions. roelandkim Forms 2 07-03-2003 10:22 AM




All times are GMT -8. The time now is 11:24 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World