Implementing A Junction Table

M

Marcy

I am trying to build a database to keep track of training topics completed by
people in my department. Our department has a set of 37 training topics. There
are 7 job classifications in the department. Each job classification has a group
of required training topics. Some of the training topics are required by more
than one job classification. I have the following tables:
TblJob
JobID
JobDescription

TblTopic
TopicID
Subject

TblRequiredJobTopic
RequiredJobTopicID
JobID
TopicID

TblTrainingCompleted
TrainingCompletedID
EmployeeID
DateCompleted
RequiredJobTopicID

I want to use a continuous form to add the records to TblTrainingCompleted. I'm
stuck on how to enter the RequiredJobTopicID field. It seems to me that I need
two comboboxes, one to define JobID and the other to define TopicID, so as to
define RequiredJobTopicID from the junction table, TblRequiredJobTopic. In a
continuous form they both need to be bound to hold their value and I can't
figure out how to implement them. I appreciate any help anyone can give me.

Marcy
 
B

Bas Cost Budde

Marcy said:
TblRequiredJobTopic
RequiredJobTopicID
JobID
TopicID
Click to expand...

Unless you can have a combination of jobID and topicID more than once, I
advise *against* the use of a separate key for this table. jobID and
topicID together seem a perfect primary key for this table. That solves
your subform problem in one step, too.
TblTrainingCompleted
TrainingCompletedID
EmployeeID
DateCompleted
RequiredJobTopicID
Click to expand...

that will become jobID+topicID;
I want to use a continuous form to add the records to TblTrainingCompleted. I'm
stuck on how to enter the RequiredJobTopicID field. It seems to me that I need
two comboboxes, one to define JobID and the other to define TopicID, so as to
define RequiredJobTopicID from the junction table, TblRequiredJobTopic.
Click to expand...

These comboboxes will now simply 'sit' on their respective fields. You
can draw from the job and topic tables for their rowsource as usual.
 
A

Allen Browne

Marcy, the first 3 tables you have a great, but there seems to be something
missing.

Presumably you also have an Employee table. What is the relationship between
Employee and Job? Does an employee only hold one job at a time? Or can an
employee have multiple jobs (e.g. part time in different jobs, or holding
multiple responsibilties at once). If multiple, you have a many-to-many
relationship between Employee and Job, so you need another junction table:
TblEmployeeJob
EmployeeID
JobID
StartDate

The TblTrainingCompleted table would have these fields:
TrainingCompletedID
EmployeeID
DateCompleted
TopicID

Now you can create a query into TblEmployeeID, TlJob, TblRequiredJobTopic,
to get a list of the topics an employee should have done. Use DISTINCT to
get only one listing of a topic where duplicated. Save this query. Use the
Unmatched query wizard to get records in TblTrainingCompleted that are not
in this query.
 
M

Marcy

I'm sorry but I don't follow whay you are saying. Are you suggesting replacing
RequiredJobTopicID in TblTrainingCompleted with two fields, JobID and TopicID
and then using independent comboboxes to fill these fields? The topics required
for a specific job would be lost.

Marcy
 
R

rkc

Allen Browne said:
Marcy, the first 3 tables you have a great, but there seems to be something
missing.

Presumably you also have an Employee table. What is the relationship between
Employee and Job? Does an employee only hold one job at a time? Or can an
employee have multiple jobs (e.g. part time in different jobs, or holding
multiple responsibilties at once). If multiple, you have a many-to-many
relationship between Employee and Job, so you need another junction table:
TblEmployeeJob
EmployeeID
JobID
StartDate

The TblTrainingCompleted table would have these fields:
TrainingCompletedID
EmployeeID
DateCompleted
TopicID
Click to expand...

Seems to me that someone should explain what it is that makes a row
in a table like the ones above unique. How is the uniqueness of the row
enforced by the database system. You can't duplicate the artificial
primary key, but you sure can duplicate everything else.

INSERT INTO TblTrainingCompleted VALUES (1, 2, "6/23/2003", 5)
INSERT INTO TblTrainingCompleted VALUES (2, 2, "6/23/2003", 5)
 
M

Marcy

Allen,

This seems fine for the required topics but there's no way to record
crosstraining, ie, an employee receives training in a topic that is not required
by his jib classification.

Marcy
 
R

Rolls

If you have a many-many relationship between employees and topics you need
to add tblEmployee-Topic to resolve the ambiguity.

Plse post to 1 group at a time.
 
B

Bas Cost Budde

Marcy said:
I'm sorry but I don't follow whay you are saying. Are you suggesting replacing
RequiredJobTopicID in TblTrainingCompleted with two fields, JobID and TopicID
and then using independent comboboxes to fill these fields? The topics required
for a specific job would be lost.
Click to expand...

That is indeed what I am saying. But you will create relationships
between those tables, effectively requiring any combination of jobID and
topicID to be inserted into tblTrainingCompleted to exist in
tblRequiredJobTopic.
 
A

Allen Browne

If TblTrainingCompleted has a foreign key to TopicID, what is to stop you
recording *any* topic an employee has taken?

Are you saying the proposed interface will not provide for the user to do
that? So you need another form to do that as well?
 
Top