"Specify the table containg the records you want to delete" messag

[Tammy]

Hi,

I am pretty familiar with Access, but not with SQL, so I wasn't able to try
any of the solutions offered in other posts.

My user has a database that keeps track of students, and would like to
delete the students who have graduated. I have changed the relationships to
"Cascade Delete Related Records" and am attempting to delete a group of
records using a Delete query.

I need to base the deletion on criteria from a table other than the
"parent", and therefore have included those fields and criteria in the
design, along with the primary key from the parent table. When I run the
delete query, I get the message "Specify the table containing the records you
want to delete." (I tried this with all the fields from all of the tables
included in the design, but received the same warning.) The table row already
includes the table names, so I'm assuming Access is confused because there is
more than one table listed.

I have run these types of queries before, but have never seen this warning.
I must not have based criteria on a different table.

So, I guess the question is - can I do this without SQL? If so, how? If not,
I'd really appreciate it if you are able to explain what I should do in the
SQL window.

The "parent" table is called: tblPersonalInfo; the primary key is called:
strID.
The related table containing the fields that criteria is based on is called:
tblProfileStatus; the fields being used as criteria are called:
ysnCurrentStudent and ysnApplicant - the criteria is set to "No."

Any matching ID (and the related data) should be deleted from the parent and
*all* child tables. (So, delete all of the records from every table if that
ID met the "no" criteria.)

I don't think it matters here, but we are using Access 2007.

Thanks for any tips you are able to provide!

 

[John Spencer]

One method involves the use of a subquery.

DELETE
FROM tblPersonalInfo
WHERE strID in
(SELECT ID
FROM tblProfileStatus
WHERE ysnCurrentStudent = False and ysnApplicant = False)

I'm not sure how your tables are set up. If you have set up
relationships to tblPersonalInfo and have set Cascade deletes to true
then all you should need is to delete the records in tblPersonalInfo and
all the related records in the dependent (child/many side) tables should
also be deleted.

If you haven't set up the relationships, then you are going to need to
run a series of queries to delete the dependent records.

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

 

[Tammy]

Hi John,

Thank you very much for taking the time to answer my post.

I did have relationships set up, and cascade deletes was set to true. I
copied and pasted your statement below into the SQL window, and was able to
perform the deletions.

Thanks again for your help with this! I don't have a "SQL" background, so
really appreciated you sending me the code needed.

Have a great week!

 

[Mike]

Thank you! One year later and your answer still works!