Delete Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
Dear Shanin:

OK, then you don't have an extensive hierarchy of table relationships.
Thus, you should be able to do them in any order.

In tha macro, you have options RunSQL and OpenQuery. See online help
for details.

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


Quick run down of my tables and relationships.

Main table: tblEmployees

Supporting Tables: tblTrainings, tblPIDS, tblEvaluation, tblWage, tblMed/Chart, tblPositionProfile, tblResignation

All the supporting tables are linked to the tblEmployees by the EmployeeID, and none of them are linked to each other, only the tblEmployees. I do have tables such as TrainingList and ResignationReasons, but they are basically there just for the purpose of being able to use a combo box in the form to choose which applies.

I currently have the delete query setup taking the field [tblEmployees]![separated] and having the criteria set to yes. I then just have the asterik dragged down for the relating table so it deletes everything for those employees with separation as yes. I have one set up for each supporting table except tblResignation and tblWage, since we will still use that info.

What selection in the Macro do you use to select and run a query. I would like to associate this Macro with a command button on a switchboard.

Tom Ellison said:
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
Click to expand...
Click to expand...
 
Back
Top