Cascade Update & Delete

[Karen]

I'm setting up a new DB for my employee training records. I have a table for
employees and a table for departments. There is a one-to-many relationship
in the department table (one) and in the employee table (many) for department
number. Let's say that when an employee leaves the company, I want to delete
all of his or her records. Should I select "Cascade Delete Related Records"
in the edit relationships dialog box if I want to delete all the records at
once or is there another way to do this?

Thank you, Karen

 

[KARL DEWEY]

Check with your HR. I would not recommend deleting training records for
departed employees but just have a field to flag that they have left.

You need two more tables one of which is TrainingCourses.

Another table, TrainingRecord, would be for EmpID, TngCrs, and Date.

You need a one-to-many from Employee to TrainingRecord and one-to-many from
TrainingCourses to TrainingRecord.

 

[John Vinson]

I'm setting up a new DB for my employee training records. I have a table for
employees and a table for departments. There is a one-to-many relationship
in the department table (one) and in the employee table (many) for department
number. Let's say that when an employee leaves the company, I want to delete
all of his or her records. Should I select "Cascade Delete Related Records"
in the edit relationships dialog box if I want to delete all the records at
once or is there another way to do this?

Thank you, Karen

I'm confused.

It sounds like you have a single record for each employee; a
Department may have many employees, but each employee would be entered
in the database only once.

If an employee leaves the company, you'ld delete that one record.

You probably would not WANT cascade deletes set on the
department-employee relationship; what this would do is cause all
employee information to be deleted for every employee in a department
if that department record should be deleted. Even if you fire them
all, you'll probably want to keep their data around for a while after
the department is disbanded!

Could you explain what you mean by "all of his or her records"? Are
there additional tables involved which you haven't mentioned? And are
you QUITE CERTAIN that you want to delete them if so? For instance, if
you have payroll records, might you not need the data they contain at
tax time?

John W. Vinson[MVP]

 

[BruceM]

Having designed a successful training records database myself (with much
help from these groups) I will first point out that you may well be required
to maintain training records for some period of time. I agree with the
suggestion to flag an employee as Inactive, and to leave the records in
place. I will also say that you may not need to link from the employee
record to a department record. You could probably just store the department
name in the employee record. You could query the employee table to find all
employees from a particular department.

 

[Karen]

Thank you for your help Bruce - Since you have created an employee training
record DB, would you please give me some advice regarding my table design?

This is what I have in terms of tables:

tblEmployees
LastName
FirstName
EmployeeNo (PK)
DepartmentNo
StartDate
EndDate
ActiveEmployee
InactiveEmployee

tblSOPs
SOPNo
SOPTitle
SOPType
LSNo (PK)

Note: We have recently changed numbers for our SOPs and I have to add all
the new numbers (LSNo) and still have the old numbers (SOPNo) in the DB, but
use the LS numbers in the form for data entry.

tblTrainingType
AutoNumber (PK)
TrainingType

tblDepartment
DeptNo (PK)
DeptName

ANY advice would be greatly appreciated
Thank you, Karen

 

[Bruce Rusk]

RE: Departments:

Why not normalize the Department field (keeping them in a separate table),
but allow for "inactive/disbanded" departments, just as you allow for
inactive employees?

 

[BruceM]

As I said, I was able to create a successful database because of the good
folks here. You have been given useful ideas by everybody who has posted so
far in this thread. I will try to summarize a few things.
First, I don't know what SOP is (other than Standard Operating Procedure),
nor do I know what an LS number might be, so I don't understand the intent
of that table.
You could set up a one-to-many between a department table and an Employee
table, but you most certainly do not want to cascade delete for reasons
already mentioned. I stored the department name in the Employee table in my
database. I realize that in the (unlikely) event a department name changes
I will have to use an update query or something to change the affected
employee records, but with fewer than 100 employees at any one time and
relatively low turnover it is a very manageable contingency. There's no
single answer, but if I had to make a rule I would say that when in doubt
you should link to the other table. Either way would work.
Assuming that each employee could attend many (i.e. more than one) training
sessions, and each training session or course could be attended by many
employees, then there is a many-to-many relationship between employees and
training sessions. In order to make that relationship possible you would
use a third table (a junction table is one term for it) between the other
two tables. Here's how I have it set up.

tblEmployee
EmployeeID (PK)
FirstName
LastName
etc.

tblSession
SessionID (PK)
Instructor
Subject
etc.

tblEnrollment (junction table)
EnrollmentID (PK)
EmployeeID (foreign key, or FK)
SessionID (FK)
SessionDate

If everybody invariably attends on the same day then SessionDate could be in
tblSession. There is a one-to-many relationship between the two EmployeeID
fields and the two SessionID fields. Check the box for Enforce Referential
Integrity. You can cascade delete in the SessionID relationship, since if a
class is entered but then cancelled you do not want to keep the roster for
that class. In general, be very careful with cascade delete.
Create a form (frmSession) based on tblSession, and another (fsubEnrollment)
based on tblEnrollment. fsubEnrollment will be the subform. On it, create
a combo box based on tblEmployee. In the row source query, use EmployeeID
as the first column. For the second column you could do something like
this: FullName: [LastName] & ", " & [FirstName]. In the third column you
could place LastName, and set Sort By to Ascending. In the combo box
properties set the column count to 2, the bound column to 1, and the column
widths to 0";1" (or whatever you need for the second number). The
EmployeeID will be stored, but the name will appear on the form. You will
probably want to set the default view for fsubEnrollment to Continuous.
With frmSession open in design view, drag the icon for fsubEnrollment onto
frmSession. Switch to form view, and try adding some test records.
Note that you need to have the name in tblEmployees before you can add that
employee to a session. You will need a separate form to manage employee
information.
Two more points: where I have said to use a table as the basis for a form,
you could use a query based on that table instead (in case you want to sort
records, or concatenate fields, or perform calculations, etc.). Also, in
tblEmployees you may want to just have a check box (Yes/No field) for
Inactive, and skip the Active field. An Employee who is not inactive is
active by default. With two check boxes you run the risk of having the
employee be active and inactive simultaneously, unless you use some sort of
code to prevent it. Not a big deal to do so, but quite possibly it is
unnecessary.

 

[Fred Boer]

Dear BruceM:

Slightly off-topic, but I was wondering if you had a mechanism in your
application to track attendance? If so, would you be willing to describe how
you did it? I'm a teacher, working on a little class "daybook" application
and I'm interested in ways that this might be accomplished.

Thanks!
Fred Boer

 

[Karen]

You are right about the SOP table, they are Standard Operating Procedures.
The SOP is what each employee is trained on. There are different training
types for SOPs: Annual, 6 month, etc. We have recently changed our SOP
numbers to a different number (LS Number). Right now we are in the middle of
the transition of switching over to the new LS number. Currently, every SOP
has an LS number. When someone writes a new procedure, there will be ONLY an
LS number and not the old SOP number. How would I do that? I have to have a
field for "SOPNo." and for "LSNo." - should I just leave the SOPNO. field
blank when a new procedure is written? I would want to make it known that
this is a new procedure and it doesn't have an SOP number assigned. Just so
the user doesn't think that the field was left blank. I hope I'm making
sense here. I'm learning.
ANY help would be greatly appreciated, Karen

As I said, I was able to create a successful database because of the good
folks here. You have been given useful ideas by everybody who has posted so
far in this thread. I will try to summarize a few things.
First, I don't know what SOP is (other than Standard Operating Procedure),
nor do I know what an LS number might be, so I don't understand the intent
of that table.
You could set up a one-to-many between a department table and an Employee
table, but you most certainly do not want to cascade delete for reasons
already mentioned. I stored the department name in the Employee table in my
database. I realize that in the (unlikely) event a department name changes
I will have to use an update query or something to change the affected
employee records, but with fewer than 100 employees at any one time and
relatively low turnover it is a very manageable contingency. There's no
single answer, but if I had to make a rule I would say that when in doubt
you should link to the other table. Either way would work.
Assuming that each employee could attend many (i.e. more than one) training
sessions, and each training session or course could be attended by many
employees, then there is a many-to-many relationship between employees and
training sessions. In order to make that relationship possible you would
use a third table (a junction table is one term for it) between the other
two tables. Here's how I have it set up.

tblEmployee
EmployeeID (PK)
FirstName
LastName
etc.

tblSession
SessionID (PK)
Instructor
Subject
etc.

tblEnrollment (junction table)
EnrollmentID (PK)
EmployeeID (foreign key, or FK)
SessionID (FK)
SessionDate

If everybody invariably attends on the same day then SessionDate could be in
tblSession. There is a one-to-many relationship between the two EmployeeID
fields and the two SessionID fields. Check the box for Enforce Referential
Integrity. You can cascade delete in the SessionID relationship, since if a
class is entered but then cancelled you do not want to keep the roster for
that class. In general, be very careful with cascade delete.
Create a form (frmSession) based on tblSession, and another (fsubEnrollment)
based on tblEnrollment. fsubEnrollment will be the subform. On it, create
a combo box based on tblEmployee. In the row source query, use EmployeeID
as the first column. For the second column you could do something like
this: FullName: [LastName] & ", " & [FirstName]. In the third column you
could place LastName, and set Sort By to Ascending. In the combo box
properties set the column count to 2, the bound column to 1, and the column
widths to 0";1" (or whatever you need for the second number). The
EmployeeID will be stored, but the name will appear on the form. You will
probably want to set the default view for fsubEnrollment to Continuous.
With frmSession open in design view, drag the icon for fsubEnrollment onto
frmSession. Switch to form view, and try adding some test records.
Note that you need to have the name in tblEmployees before you can add that
employee to a session. You will need a separate form to manage employee
information.
Two more points: where I have said to use a table as the basis for a form,
you could use a query based on that table instead (in case you want to sort
records, or concatenate fields, or perform calculations, etc.). Also, in
tblEmployees you may want to just have a check box (Yes/No field) for
Inactive, and skip the Active field. An Employee who is not inactive is
active by default. With two check boxes you run the risk of having the
employee be active and inactive simultaneously, unless you use some sort of
code to prevent it. Not a big deal to do so, but quite possibly it is
unnecessary.

Thank you for your help Bruce - Since you have created an employee
training
record DB, would you please give me some advice regarding my table design?

This is what I have in terms of tables:

tblEmployees
LastName
FirstName
EmployeeNo (PK)
DepartmentNo
StartDate
EndDate
ActiveEmployee
InactiveEmployee

tblSOPs
SOPNo
SOPTitle
SOPType
LSNo (PK)

Note: We have recently changed numbers for our SOPs and I have to add all
the new numbers (LSNo) and still have the old numbers (SOPNo) in the DB,
but
use the LS numbers in the form for data entry.

tblTrainingType
AutoNumber (PK)
TrainingType

tblDepartment
DeptNo (PK)
DeptName

ANY advice would be greatly appreciated
Thank you, Karen

 

[BruceM]

OK, let me suggest something here. It seems as if each SOP will be the
subject of multiple training sessions over time. Something like the
structure I suggested should work for you. The difference is that where I
have suggested Subject in tblSession, you could instead use SOP. Your
method for creating a new Procedure could include provisions for handling
the numbering system change, but that is separate from recording training
session information.
Here are a few more questions for you. Are SOPs ever revised? If so, are
the revisions identified (Rev. A, Rev. B, etc.)? Are the SOPs themselves
stored in the database, or are they just indexed there? I see where you
would need tblSOPs, but I don't think that is where you should be keeping
the training information. Instead, try setting up something such as I
described. Once that is done, go to frmSession and use the text box bound
to the SOP (or Subject) field to enter the SOP. If the basic structure
works OK we can look at linking to the SOP record.
I think you will want to use a separate form to enter SOP information, just
as you would use a separate form for Employee information. On that SOP form
you could use code to hide the text box bound to SOPNos. That code could go
in the form's Current event, and would look something like this:

If Me.NewRecord or IsNull(Me.SOPNo) Then
Me.txtSOPNo.Visible = False
Else
Me.txtSOPNo.Visible = True
End If

The word Else and the following line may not be necessary. txtSOPNo is the
text box bound to SOPNo.

I urge you to give a try to the basic structure I have suggested. Once you
have cleared up a few questions I have on the SOPs we can work on
incorporating the SOP table.

You are right about the SOP table, they are Standard Operating Procedures.
The SOP is what each employee is trained on. There are different training
types for SOPs: Annual, 6 month, etc. We have recently changed our SOP
numbers to a different number (LS Number). Right now we are in the middle
of
the transition of switching over to the new LS number. Currently, every
SOP
has an LS number. When someone writes a new procedure, there will be ONLY
an
LS number and not the old SOP number. How would I do that? I have to
have a
field for "SOPNo." and for "LSNo." - should I just leave the SOPNO. field
blank when a new procedure is written? I would want to make it known that
this is a new procedure and it doesn't have an SOP number assigned. Just
so
the user doesn't think that the field was left blank. I hope I'm making
sense here. I'm learning.
ANY help would be greatly appreciated, Karen

As I said, I was able to create a successful database because of the good
folks here. You have been given useful ideas by everybody who has posted
so
far in this thread. I will try to summarize a few things.
First, I don't know what SOP is (other than Standard Operating
Procedure),
nor do I know what an LS number might be, so I don't understand the
intent
of that table.
You could set up a one-to-many between a department table and an Employee
table, but you most certainly do not want to cascade delete for reasons
already mentioned. I stored the department name in the Employee table in
my
database. I realize that in the (unlikely) event a department name
changes
I will have to use an update query or something to change the affected
employee records, but with fewer than 100 employees at any one time and
relatively low turnover it is a very manageable contingency. There's no
single answer, but if I had to make a rule I would say that when in doubt
you should link to the other table. Either way would work.
Assuming that each employee could attend many (i.e. more than one)
training
sessions, and each training session or course could be attended by many
employees, then there is a many-to-many relationship between employees
and
training sessions. In order to make that relationship possible you would
use a third table (a junction table is one term for it) between the other
two tables. Here's how I have it set up.

tblEmployee
EmployeeID (PK)
FirstName
LastName
etc.

tblSession
SessionID (PK)
Instructor
Subject
etc.

tblEnrollment (junction table)
EnrollmentID (PK)
EmployeeID (foreign key, or FK)
SessionID (FK)
SessionDate

If everybody invariably attends on the same day then SessionDate could be
in
tblSession. There is a one-to-many relationship between the two
EmployeeID
fields and the two SessionID fields. Check the box for Enforce
Referential
Integrity. You can cascade delete in the SessionID relationship, since
if a
class is entered but then cancelled you do not want to keep the roster
for
that class. In general, be very careful with cascade delete.
Create a form (frmSession) based on tblSession, and another
(fsubEnrollment)
based on tblEnrollment. fsubEnrollment will be the subform. On it,
create
a combo box based on tblEmployee. In the row source query, use
EmployeeID
as the first column. For the second column you could do something like
this: FullName: [LastName] & ", " & [FirstName]. In the third column
you
could place LastName, and set Sort By to Ascending. In the combo box
properties set the column count to 2, the bound column to 1, and the
column
widths to 0";1" (or whatever you need for the second number). The
EmployeeID will be stored, but the name will appear on the form. You
will
probably want to set the default view for fsubEnrollment to Continuous.
With frmSession open in design view, drag the icon for fsubEnrollment
onto
frmSession. Switch to form view, and try adding some test records.
Note that you need to have the name in tblEmployees before you can add
that
employee to a session. You will need a separate form to manage employee
information.
Two more points: where I have said to use a table as the basis for a
form,
you could use a query based on that table instead (in case you want to
sort
records, or concatenate fields, or perform calculations, etc.). Also, in
tblEmployees you may want to just have a check box (Yes/No field) for
Inactive, and skip the Active field. An Employee who is not inactive is
active by default. With two check boxes you run the risk of having the
employee be active and inactive simultaneously, unless you use some sort
of
code to prevent it. Not a big deal to do so, but quite possibly it is
unnecessary.

Thank you for your help Bruce - Since you have created an employee
training
record DB, would you please give me some advice regarding my table
design?

This is what I have in terms of tables:

tblEmployees
LastName
FirstName
EmployeeNo (PK)
DepartmentNo
StartDate
EndDate
ActiveEmployee
InactiveEmployee

tblSOPs
SOPNo
SOPTitle
SOPType
LSNo (PK)

Note: We have recently changed numbers for our SOPs and I have to add
all
the new numbers (LSNo) and still have the old numbers (SOPNo) in the
DB,
but
use the LS numbers in the form for data entry.

tblTrainingType
AutoNumber (PK)
TrainingType

tblDepartment
DeptNo (PK)
DeptName

ANY advice would be greatly appreciated
Thank you, Karen

:

Having designed a successful training records database myself (with
much
help from these groups) I will first point out that you may well be
required
to maintain training records for some period of time. I agree with
the
suggestion to flag an employee as Inactive, and to leave the records
in
place. I will also say that you may not need to link from the
employee
record to a department record. You could probably just store the
department
name in the employee record. You could query the employee table to
find
all
employees from a particular department.

I'm setting up a new DB for my employee training records. I have a
table
for
employees and a table for departments. There is a one-to-many
relationship
in the department table (one) and in the employee table (many) for
department
number. Let's say that when an employee leaves the company, I want
to
delete
all of his or her records. Should I select "Cascade Delete Related
Records"
in the edit relationships dialog box if I want to delete all the
records
at
once or is there another way to do this?

Thank you, Karen

 

[BruceM]

Fred,

No, I don't track attendance. Training is not really done by way of
structured classes, but is instead more often along the lines of on-the-job
training regarding new or revised manufacturing, auditing, inspection, etc.
methods. Participation in training is very much up to supervisory and
management decisions about who will need to know about the changes or
revisions. I do not use the database to track or monitor attendance, so I
can offer no suggestions based on my own experience.
However, I know that variants of this question arise from time to time. A
Google groups search may help uncover some past discussions, or you could
try a new thread. If you continue in this thread it is likely your question
will get buried. Good luck.