Deleting specific records based on query results

[scifinut]

Is there a way to use a select query to find and then delete specific records
in a database. My database records basic applicant information in one table
(tblApplicants) and any interview data in another (TblActivities). I need to
do an annual purge of applicants from a previous year who were never
interviewed. I have a select query that returns the name and date entered
from the TblApplicants table where the requisition data in the TblActivities
table is null. I know I can then go to the TblApplicants and manually delete
each record, but at over 1700 records that seems a little crazy if there's a
better way.

 

[Jeff Boyce]

You've decided "how" ... now, why?

What will having the uninterviewed Applicants removed then allow you to do?

What problem are you trying to solve using this approach?

I ask, not out of prurient interest, but because there may be a solution
other than the one you've selected...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[PvdG42]

Is there a way to use a select query to find and then delete specific
records
in a database. My database records basic applicant information in one
table
(tblApplicants) and any interview data in another (TblActivities). I need
to
do an annual purge of applicants from a previous year who were never
interviewed. I have a select query that returns the name and date entered
from the TblApplicants table where the requisition data in the
TblActivities
table is null. I know I can then go to the TblApplicants and manually
delete
each record, but at over 1700 records that seems a little crazy if there's
a
better way.

Yes, you should be able to do what you want using SQL syntax similar to
this:


DELETE FROM tblApplicants
WHERE <then, add the criteria from your SELECT query that correctly
identifies the rows to be deleted>

 

[John Spencer]

Assumptions:
tblActivities has a field identifying the applicant

If tblActivities has multiple records per applicant then you need to purge the
records from tblActivities before you can purge the records from tblApplicants
unless you have Cascade delete set on the relationship between the two tables.

DELETE
FROM tblApplicants
WHERE Not Exists
(SELECT * FROM tblActivities
WHERE tblActivities.ApplicantID = tblApplicants.ApplicantID)

If you post the SQL of your current query (View: SQL from design view) someone
can probably use that to build you a more specific query.

Also, IF you run the current query you might be able to select multiple
records at one time and delete the selection all at one time.

If you have an activity type, you might need to expand the subquery to include
criteria to filter the records based on the activity type and perhaps on the
activity date.

DELETE
FROM tblApplicants
WHERE Not Exists
(SELECT * FROM tblActivities
WHERE tblActivities.ApplicantID = tblApplicants.ApplicantID
AND tblActivities.ActivityType = "Interview")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County