Re: Delete Query

From: Tom Ellison (tellison_at_jcdoyle.com)
Date: 07/30/04 

Date: Fri, 30 Jul 2004 12:56:10 -0500

Dear Shanin:

That is clearer. What I posted applies to deleting dependeing rows
from a set of related tables nonetheless.

As far as having me write the queries, I don't have access to your
table designs. I don't know specifically what you need. Apparently,
you'd need to delete using a filter criterion of the EmployeeID
selected in a form. This could be accomplished with saved queries
that reference a control on the form then. Stringing them together in
a macro or in VBA code would be rather simple if the relational
diagram I explained is itself quite simple. But yes, I expect I could
do a project like this without any trouble. However, the free help
here is intended more to teach you how to do it yourself.

Were you able to follow and apply what I posted before? Are there
some specific questions you could ask about what you need to know to
implement it? If so, I think you'll be able to get the help you need
here just fine.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

On Fri, 30 Jul 2004 10:01:03 -0700, Shanin
<Shanin@discussions.microsoft.com> wrote:

>Actually I do have cascading deletes selected, but I don't plan on deleting the employee from the tblEmployees. We still want to be able to pull up information in order to do employment verification. Once they leave though, we know longer need to know all their trainings, etc, so I want to delete them from that. All the tables are only related to the tblEmployees and not to each other. Is there a macro or code you could write that would assign running all of those queries one after another?
>
>"Tom Ellison" wrote:
>
>> Dear Shanin:
>>
>> A simple approach might work. If you set Cascade Delete in all the
>> relationships in the same dialog where you set up the referential
>> integrity, then when you delete the row from the Employee table all
>> the dependent rows in the tables will be removed as well.
>>
>> There are some special circumstances in which this cannot be done, but
>> it will tell you this when you try to implement the Cascade Deletes.
>> However, you should take a bit of care. Use a copy of the database to
>> set this up, and test it.
>>
>> Another approach is to write delete queries to be run when you remove
>> an employee. These queries must start at the "top" of the "tree" of
>> related tables.
>>
>> On a piece of paper, put the Employee table at the bottom. Add all
>> the tables that depend on this table (that have EmployeeID as a single
>> part referential key) as "branches" of that table, putting them above
>> the Employee table on your paper. Next, consider whether there are
>> tables that depend on these first-level branches. If so, add them
>> similarly.
>>
>> The point of this exercise is that you cannot delete a row from a
>> "parent" table unless all the related rows in all "child" tables have
>> been removed FIRST. The diagram gives you a visual reference to see
>> this. You can also do this in the Relationship Diagram for your
>> system. I usually organize this left-to-right instead of
>> bottom-to-top, but the important point is to add the "tree"
>> organization to the diagram so it is readable as the hierarchy it
>> represents, with parent on the left and child on the right.
>>
>> From this you can quickly derive the order in which tables must have
>> rows deleted. You cannot leave a "child" as an orphan even though you
>> intend to come back later and remove that child row. The rows of the
>> child tables must be removed first or you have created an "orphan"
>> which violates the referential integrity rules you've set up.
>>
>> Anyway, my guess is that this is one problem you must address.
>> However, in no case could this be done in one delete query. It would
>> be a series of delete queries, or you can use cascading as already
>> described.
>>
>> Tom Ellison
>> Microsoft Access MVP
>> Ellison Enterprises - Your One Stop IT Experts
>>
>>
>> On Fri, 30 Jul 2004 07:45:01 -0700, Shanin
>> <Shanin@discussions.microsoft.com> wrote:
>>
>> >I was wondering how this would be done. Currently I have a database set up tracking employees and all kinds of training, disciplinary, errors, etc. They are all linked to the main Employees table by the EmployeeID with referential integrity on. The EmployeeID is or is part of the primary key in all of my other tables. On my Employees table I have a yes/no box for if they are separated. What I was wanting to know, since I don't want to delete the past employee basic info such as wage and reason for leaving, is how I can make one delete query that would delete all their info from these other tables, or at least a one button push to do it.
>> >
>> >I tried combining them all in one delete query but got the statement couldn't delete from specified tables. Currently I have a separate delete query for each table, which means I would have to run about 7 different queries to get the results I was wanting.
>> >Thanks
>>
>>