Delete Query Error

HBill

New member
Local time
Today, 04:55
Joined
Nov 3, 2015
Messages
6
I have some Delete Query code taken from the Access query GUI which brings up 'Specify the table containing the records you want to delete'. (I have set up complete referential integrity in all joins). Unfortunately I do not yet understand SQL very well, but after reading your forums I believe there are ways to re-write this code to make it work. Because it is a 'one off'' the code does not have to be efficient or quick. I'd be very grateful if anyone can re-code it for me.

DELETE Employees.[Employment Status], Availability.[Available From]
FROM Employees INNER JOIN Availability ON Employees.[Employee ID] = Availability.[Employee ID]
WHERE (((Employees.[Employment Status])="Potential") AND ((Availability.[Available From])<#1/1/2012#));
 
Last edited:
But the problem is that we don't know what table you want to delete from. If that was clear from the code you posted, you wouldn't get an error like, 'Specify the table containing the records you want to delete'.

One thing is that you don't need to call out any fields in a delete query.

But what, exactly, are you trying to do?
 
OK - I have a table Employees and a table Availability. They are joined by EmployeeID - one to many.
For each Employee record, one field defines them as Current, Past or Potential.
In the Availability table a date field defines from when they were first available.
I want to delete Employee records defined as Potential, but only where the matching EmployeeID in table Availability has a date prior to 01/01/2012.
So I want to delete the same employee records in two tables
I created a Delete query using the design facility in Access then posted the resulting SQL code as above
Thanks for your comments - I hope this clarifies
 
Do you have the relationship between that tables set to cascade delete? If so, then deleting the Employee will automatically delete the availability records.

And in that case, try this . . .
Code:
DELETE FROM Employees A t
WHERE t.Status = "Potential"
AND 
   ( 
   SELECT Count(*) 
   FROM Availability 
   WHERE EmployeeID = t.EmployeeID
   AND AvailableFrom < #1/1/12#
   ) > 0
See what's going on there?
 
I have cascade delete in place but I get an error 'Syntax error in From clause'.
I copied and pasted directly using Notepad.
Thanks
 

Users who are viewing this thread

Back
Top Bottom