Data Type Conflict

[Karen]

I have two tables with the following fields:

tblEmployees
EmployeeID (Number) - Formatted "00000"
FirstName
LastName
StartDate
EndDate
DepartmentID

tblTrainingEvents
TrainingID
EmployeeID (Text) contains a Lookup with the following in the row source:
SELECT [LastName] & ", " & [FirstName] AS Employee FROM tblEmployees ORDER
BY [LastName] & ", " & [FirstName];
SOPID
TrainingDate

There was a one-to-many relationship in the EmployeeID field
One (tblEmployees)
Many (tblTrainingEvents)

I had to delete the relationship in order to make a change. So right now
there is no relationship assigned.

My problem is: I want to create the relationship and enforce referential
integrity, but the data types have to be the same. I don't know if you need
more info to answer this question. If not, what should I do? Should I just
create the relationship and not enforce referential integrity or should other
changes be made? I have to do some research about referential integrity - Is
it important in this case?

HELP!

 

[TedMi]

Yes, referential integrity is important. You don't want to have training
events for non-existent employees. The linking fields, EmployeeID, should be
the same type in both tables. Why are they different?

 

[Karen]

Thank you for your help - There have been so many changes, I don't remember
why they are different. I don't know what to do. Would you please help me?
I have to leave for the day. I'll check back tomorrow.
Thank you, Karen

 

[TedMi]

First of all, it seems that the table design is not quite correct. You have 2
basic entities: employees, and training events. Your current design allows
recording the attendance of each employee at multiple training events. But
also each training event can be attended by mutliple employees. That is a
many-to-many relationship, and should be set up as follows:
tblEmployees looks OK - it holds employee information without any training
data. Then there should be a table holding just the info about each training
event, without any employee data:
tblTrainingEvent
TrainingID (Numeric)
Training Subject
DateStart
DateEnd
Location
Instructor
MaxAttendance
Any other info specific to the event

Then a third table should link multiple employees to multiple events; let's
call it lnkEmpEvent:
EmployeeID (same type as in tblEmployee)
TrainingID (same type as in tblTrainingEvent)
GradeObtained
Any other info specific to this employee at this event (e.g. who pays
for it).

Set up a One-to-Many from tblEmployee to lnkEmpEvent on EmployeeID,
and a One-to-Many from tblTrainingEvent to lnkEmpEvent on TrainingID,
enforcing referential integrity. What to do about departing employees is a
business rule decision - do you delete that employee's links to training
events? Or do you keep them, so that the training staff can keep track of
their activities even for departed employees? In the latter case, you should
disallow deleting employees with links to training events, but provide some
way of marking employees as current/former.

 

[Karen]

Thank you - I already have data in all the tables. I would have do more data
entry. If my design is wrong, additional data entry is inevitable. If you
could kindly look at me design before I make the changes to let me know if
it's set up properly. I would greatly appreciate it. If you need to know
more info about the design please let me know. Is it possible to e-mail you
an image of my relationships? Please let me know. It's easier than trying
to explain each relationship. Thank you

Below is my table structure:
Is this structure wrong?

tblEmployees
EmployeeID (PK)
FirstName
LastName
StartDate
EndDate
DepartmentID

tblDepartment
DepartmentID (PK)
DepartmentName

tblDepartmentCategoryJoin
DeptID (PK)
CategoryID (PK)

tblCategory
Category (PK)

tblSOPs
ID (PK) - AutoNumber
SOPID
SOPTitle
CategoryID

tblTrainingEvents
TrainingID (PK) - AutoNumber
EmployeeID
SOPID
TrainingDate

 

[TedMi]

One obvious error is in tblSOP.
Why have the ID (Autonumber) *and* SOPID? I assume that the latter is
already unique. Furthermore, I'm also assuming that the relationship
tblTrainingEvent --> tblSOP is via the fields named SOPID in each table.
Meaning that the Autonumber ID is not used for anything. If my assumptions
are correct, then remove the ID field and make SOPID the PK.
Potentially, there is a more serious problem with tblTrainingEvents -
depending on how your enterprise defines a training event. Is such an event a
group activity, like a class, course or seminar? Or is the training of a
single employee an event separate from the training of any other employee?
Your current design supports the latter. If that is what you intend, then the
design is basically correct. Otherwise, it needs a basic overhaul and
possibly major data re-entry. But there may be a way to salvage the existing
data from tblTrainingEvents, depending on the semantics of the data contained
therein. Let me know how you percieve training events, and we'll take it from
there.

 

[Karen]

Thank you again for all your help - I was following your first set of
instructions:
"There should be a table holding just the info about each training
event, without any employee data."
I did that - Then your second set of instructions: "A third table should
link multiple employees to multiple events; let's call it lnkEmpEvent."
EmployeeID (same type as in tblEmployee)
TrainingID (same type as in tblTrainingEvent)

One problem: My TrainingID (in tblTrainingEvent) is a AutoNumber - Is that OK

To give you a brief overview of this DB:

This is the scenario: Every task or procedure in my company has to have a
written Standard Operating Procedure (SOP) - Each employee has to be trained
on each SOP. There are different training types for each SOP. When an
employee starts, there has to be initial training. When an SOP is revised,
there has to be SOP Revision training and a few other types - therefore, I
had to add a table tblTrainingType.
There are 4 departments involved - Each department has there own SOPs that
each employee has to be trained (depending on their level) and each
department has there SOPs categorized. Department A can share certain SOPs
with Department B, Department B and C can share SOPs with Department D, etc.
Therefore, one SOP can have multiple categories.

The training is individual training - Not a group session. A team leader
trains each employee individually and we have to record the following for
that training event. Employee Name
SOPID
TrainingType
TrainingDate

These are the tables and relationships I have so far - PLEASE HELP!

tblEmployees
EmployeeID (PK)
LastName
FirstName
StartDate
EndDate
DepartmentID

tblSOPs
ID (PK) - AutoNumber
SOPID
SOPTitle
CategoryID

tblCategory
Category (PK) - (one to many with CategoryID field in
tblDepartmentCategoryJoin) and (one to many with CategoryID field in tblSOPs)

tblTrainingEvent
TrainingID (PK) - AutoNumner
TrainingType
SOPID
TrainingDate

tblTrainingType
TrainingID (PK) - AutoNumber
TrainingType

tblDepartment
DeptID (PK) - (one to many with DepartmentID field in tblEmployees) and (one
to many with DeptID field in tblDepartmentCategoryJoin)
DepartmentName

tblDepartmentCetegoryJoin
DeptID (PK)
CategoryID (PK)

You mentioned removing the ID field (in tblSOP) and make SOPID the PK.
Right now I can't because I have duplicate data in the SOPID field. I'm very
confused right now. PLEASE HELP me with the tables, fields and relationships.

Thank you

 

[TedMi]

Karen: After your latest explanation of the training environment, I see that
your initial design is not very far off the mark and needs only minor
tweaking. Some of my earlier suggestions were meant for the more traditional
group training environment and do not apply in your case. Since we are the
only ones communicating on this thread, I suggest we take it off-list. Please
e-mail me directly to continue, and I will be glad to help further.