Implementing A Junction Table

[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

[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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Marcy" <mt*****@earthlink.net> wrote in message
news:e0******************@newsread3.news.atl.earth link.net...

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

[Bas Cost Budde]

Marcy wrote:

TblRequiredJobTopic
RequiredJobTopicID
JobID
TopicID
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
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.

These comboboxes will now simply 'sit' on their respective fields. You
can draw from the job and topic tables for their rowsource as usual.
--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

[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
"Bas Cost Budde" <ba*@heuveltop.org> wrote in message
news:c1**********@news2.solcon.nl...

Marcy wrote:

TblRequiredJobTopic
RequiredJobTopicID
JobID
TopicID

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

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.

These comboboxes will now simply 'sit' on their respective fields. You
can draw from the job and topic tables for their rowsource as usual.
--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

[rkc]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...

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

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)

[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
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Marcy" <mt*****@earthlink.net> wrote in message
news:e0******************@newsread3.news.atl.earth link.net...

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

[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.

[Bas Cost Budde]

Marcy wrote:

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.

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.

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

[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?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Marcy" <mt*****@earthlink.net> wrote in message
news:9u******************@newsread3.news.atl.earth link.net...

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
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@freenews.iinet.net. au...

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.
"Marcy" <mt*****@earthlink.net> wrote in message
news:e0******************@newsread3.news.atl.earth link.net...

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