Trying to delete records from multiple tables

[OverMyHead]

I am not a programmer and thus have found it very difficult to do some of the
more complicated processes in Access using only Macros. I am currently
trying to delete records using a delete query. The primary key that links
the records in the main table to the other tables is an ID#. However, I am
wanting to delete records based on a different field in the main table. This
is for a school scholarship program, and I am needing to be able to delete
all students from the database with a certain graduation year stored in the
main table. The user needs to enter that parameter if possible.

 

[John Spencer]

You can only delete from multiple tables at one time if you have set up
relationships between the tables and checked the cascade delete box for
the relationship.

If you do that when you delete a record in the main table (the one side)
the related records in the other tables will be deleted.

If you don't do that then you need to set up a series of queries to
delete data from the tables and you need to delete records in the
related tables BEFORE you delete records in the main table.

So if you have a table (studentsInClass) that records the classes that a
student has taken, you need to run a delete query like the following.
DELETE
FROM StudentsInClass
WHERE StudentsInClass.StudentId
IN (SELECT StudentID from MainTable
WHERE GraduationYear = "2007")

Then assuming you have no other tables directly related to the Main
table you can delete the records in the main table.
DELETE
FROM MainTable
WHERE GraduationYar = "2007"

Those are generic examples since I have no idea of your actual table
structure.


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

 

[OverMyHead]

Thank you VERY much. Works great!! Now I can sleep at night...