Re: Delete Query
From: Tom Ellison (tellison_at_jcdoyle.com)
Date: 07/30/04
- Next message: G.C.Mandrake: "Re: Converting Crosstab Query to a Pass-Through Query"
- Previous message: James Randall: "Merging 2 tables, from 2 DB's JR"
- In reply to: Shanin: "Delete Query"
- Next in thread: Shanin: "Re: Delete Query"
- Reply: Shanin: "Re: Delete Query"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 30 Jul 2004 11:03:44 -0500
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
- Next message: G.C.Mandrake: "Re: Converting Crosstab Query to a Pass-Through Query"
- Previous message: James Randall: "Merging 2 tables, from 2 DB's JR"
- In reply to: Shanin: "Delete Query"
- Next in thread: Shanin: "Re: Delete Query"
- Reply: Shanin: "Re: Delete Query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
