Specify the table containing the records you want to delete

[sock]

Hi, I get this message when I try to run a delete query.
I would like to delete everything from tblVolunteer when in tblVolunteerProject.EndDate= <Date()-730. (ie basically all records 2 years old). The SQL code is this.

TDELETE tblVolunteer.VolunteerID, tblVolunteer.Surname, tblVolunteer.[First Name], tblVolunteer.Title, tblVolunteer.Gender, tblVolunteer.[Volunteer Type], tblVolunteer.[UK Address 1], tblVolunteer.[UK Address 2], tblVolunteer.[UK Address 3], tblVolunteer.[UK Post Code], tblVolunteer.[Foreign National Address 1], tblVolunteer.[Foreign National Address 2], tblVolunteer.[Foreign National address 3], tblVolunteer.[Foreign National Address Post Code/Zip], tblVolunteer.[Next of Kin], tblVolunteer.[Next of Kin Telephone Number], tblVolunteer.Religion, tblVolunteer.DOB, tblVolunteer.Age, tblVolunteer.[Home Telephone], tblVolunteer.[Mobile Telephone], tblVolunteer.[Email Address], tblVolunteer.[Subject Studied], tblVolunteer.[Interest Area], tblVolunteer.CV, tblVolunteer.Ref1, tblVolunteer.Ref2, tblVolunteer.[OHF Sent], tblVolunteer.[OHF Received], tblVolunteer.[CRB Sent], tblVolunteer.[CRB Received], tblVolunteer.[Induction Date], tblVolunteer.[Badge Received], tblVolunteer.[Ward Interview Date], tblVolunteer.[Interviewed by], tblVolunteer.[Interview Date], tblVolunteer.Remarks, tblVolunteer.[Project update], tblVolunteerProject.EndDate
FROM tblVolunteer INNER JOIN tblVolunteerProject ON tblVolunteer.VolunteerID = tblVolunteerProject.VolunteerID
WHERE (((tblVolunteerProject.EndDate)<Date()-730));

I would be grateful for any help. Thanks

 

[ByteMyzer]

You can make this much simpler on yourself with a subquery scenario:

DELETE tblVolunteer.*
FROM tblVolunteer
WHERE NOT EXISTS
(SELECT tblVolunteerProject.VolunteerID
 FROM tblVolunteerProject
 WHERE tblVolunteerProject.VolunteerID = tblVolunteer.VolunteerID
 AND   tblVolunteerProject.EndDate >= Date()-730);

 

[sock]

You can make this much simpler on yourself with a subquery scenario:

DELETE tblVolunteer.*
FROM tblVolunteer
WHERE NOT EXISTS
(SELECT tblVolunteerProject.VolunteerID
 FROM tblVolunteerProject
 WHERE tblVolunteerProject.VolunteerID = tblVolunteer.VolunteerID
 AND   tblVolunteerProject.EndDate >= Date()-730);

Hi, thank you very much. I was trying that myself after looking at a webpage by Allenbrowne which is http://allenbrowne.com/subquery-01.html#DeleteUnmatched
I never got as far as entering the AND criteria in relation to the date.
The thing is is that this way it works too well as I have a form for Volunteers and a subform for Project details. If I run this query it deletes all records older than 2 years which is great but it also deletes Volunteer profiles which are newly created but have not yet had project details assigned and so the subform is empty!

 

[ByteMyzer]

When you say, "Volunteer profiles which are newly created", which field in table tblVolunteer do you use to qualify this? Induction Date? Interview Date? Please specify.

 

[sock]

Hi, the Volunteer profile is created as soon as name and personal details are added and is assigned a number as the primary key is an autonumber. I have three tables tblVolunteer, tblProject and tblProjectVolunteer. TblProjectVolunteer has for its fields, VolunteerID, ProjectID, StartDate, Enddate and ProjectStatus. TblProjectVolunteer is the subform within frmVolunteer(tblVolunteer) and contains dropdowns for VolunteerID and ProjectID so that projects can be assigned to the Volunteer.
I did come up with a way of deleting the Volunteer details from frmVolunteer after two years. On completion of the Enddate in the subform the Project Status changes to Completed. So I placed a textbox called EndDateLatest set to invisible on frmVolunteer and added this field to tblVolunteer. An update query runs so that the EndDate on completion in the subform is added to this textbox. I then run an update query which just includes tblVolunteer from this textbox date minus 2 years so as to delete frmVolunteer at the same time as the subform details. I am now thinking on how and when to delete tblProject as some projects have more than one volunteer and they may have different project enddates and I would like to delete tblProject based on the last volunteer enddate. Thanks Richard

 

[ByteMyzer]

How about this:

DELETE tblVolunteer.*
FROM tblVolunteer
WHERE EXISTS
(SELECT T1.VolunteerID
 FROM tblVolunteerProject AS T1
 WHERE T1.VolunteerID = tblVolunteer.VolunteerID
 AND   T1.EndDate < Date()-730)
AND NOT EXISTS
(SELECT T2.VolunteerID
 FROM tblVolunteerProject AS T2
 WHERE T2.VolunteerID = tblVolunteer.VolunteerID
 AND   T2.EndDate >= Date()-730);

 

[sock]

Hi, your first example worked better for my database. Thankyou very much for your help. It is the first time I have used this forum and I have enjoyed the experience.

 

[ByteMyzer]

You're very welcome. I'm glad it worked for you, and I hope your experience here encourages you to come see us again whenever you have a question.

 

[sock]

Hi, just thought you would like to know that I did have reason to use the second code you posted. I had to change it slightly to make it work for me in this scenario. Here it is;

DELETE tblVolunteerProject.*
FROM tblVolunteerProject
WHERE EXISTS
(SELECT T1.VolunteerID
FROM tblVolunteer AS T1
WHERE T1.VolunteerID = tblVolunteerProject.VolunteerID
AND T1.CompletedDate < Date()-730)
AND EXISTS
(SELECT T2.VolunteerID
FROM tblVolunteerProject AS T2
WHERE T2.VolunteerID = tblVolunteerProject.VolunteerID
AND T2.ProjectStatus= "Did not complete");

I just wondered if there could have been an easier way for me to write this situation. Thanks Richard