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

TblRequiredJobT opic
RequiredJobTopi cID
JobID
TopicID

TblTrainingComp leted
TrainingComplet edID
EmployeeID
DateCompleted
RequiredJobTopi cID

I want to use a continuous form to add the records to TblTrainingComp leted. I'm
stuck on how to enter the RequiredJobTopi cID field. It seems to me that I need
two comboboxes, one to define JobID and the other to define TopicID, so as to
define RequiredJobTopi cID from the junction table, TblRequiredJobT opic. 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]

Re: Implementing A Junction Table

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 TblTrainingComp leted table would have these fields:
TrainingComplet edID
EmployeeID
DateCompleted
TopicID

Now you can create a query into TblEmployeeID, TlJob, TblRequiredJobT opic,
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 TblTrainingComp leted 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" <mthomas@earthl ink.net> wrote in message
news:e0_Yb.1141 1$hm4.7886@news read3.news.atl. earthlink.net.. .[color=blue]
> I am trying to build a database to keep track of training topics completed[/color]
by[color=blue]
> people in my department. Our department has a set of 37 training topics.[/color]
There[color=blue]
> are 7 job classifications in the department. Each job classification has a[/color]
group[color=blue]
> of required training topics. Some of the training topics are required by[/color]
more[color=blue]
> than one job classification. I have the following tables:
> TblJob
> JobID
> JobDescription
>
> TblTopic
> TopicID
> Subject
>
> TblRequiredJobT opic
> RequiredJobTopi cID
> JobID
> TopicID
>
> TblTrainingComp leted
> TrainingComplet edID
> EmployeeID
> DateCompleted
> RequiredJobTopi cID
>
> I want to use a continuous form to add the records to[/color]
TblTrainingComp leted. I'm[color=blue]
> stuck on how to enter the RequiredJobTopi cID field. It seems to me that I[/color]
need[color=blue]
> two comboboxes, one to define JobID and the other to define TopicID, so as[/color]
to[color=blue]
> define RequiredJobTopi cID from the junction table, TblRequiredJobT opic. In[/color]
a[color=blue]
> 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[/color]
me.[color=blue]
>
> Marcy[/color]

[Bas Cost Budde]

Re: Implementing A Junction Table

Marcy wrote:[color=blue]
> TblRequiredJobT opic
> RequiredJobTopi cID
> JobID
> TopicID[/color]

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.[color=blue]
>
> TblTrainingComp leted
> TrainingComplet edID
> EmployeeID
> DateCompleted
> RequiredJobTopi cID[/color]

that will become jobID+topicID;
[color=blue]
> I want to use a continuous form to add the records to TblTrainingComp leted. I'm
> stuck on how to enter the RequiredJobTopi cID field. It seems to me that I need
> two comboboxes, one to define JobID and the other to define TopicID, so as to
> define RequiredJobTopi cID from the junction table, TblRequiredJobT opic.[/color]

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]

Re: Implementing A Junction Table

I'm sorry but I don't follow whay you are saying. Are you suggesting replacing
RequiredJobTopi cID in TblTrainingComp leted 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" <bas@heuveltop. org> wrote in message
news:c11pv0$e9f $1@news2.solcon .nl...[color=blue]
> Marcy wrote:[color=green]
> > TblRequiredJobT opic
> > RequiredJobTopi cID
> > JobID
> > TopicID[/color]
>
> 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.[color=green]
> >
> > TblTrainingComp leted
> > TrainingComplet edID
> > EmployeeID
> > DateCompleted
> > RequiredJobTopi cID[/color]
>
> that will become jobID+topicID;
>[color=green]
> > I want to use a continuous form to add the records to TblTrainingComp leted.[/color][/color]
I'm[color=blue][color=green]
> > stuck on how to enter the RequiredJobTopi cID field. It seems to me that I[/color][/color]
need[color=blue][color=green]
> > two comboboxes, one to define JobID and the other to define TopicID, so as[/color][/color]
to[color=blue][color=green]
> > define RequiredJobTopi cID from the junction table, TblRequiredJobT opic.[/color]
>
> 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
>[/color]

[rkc]

Re: Implementing A Junction Table


"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
news:403470c7$0 $22519$5a62ac22 @freenews.iinet .net.au...[color=blue]
> Marcy, the first 3 tables you have a great, but there seems to be[/color]
something[color=blue]
> missing.
>
> Presumably you also have an Employee table. What is the relationship[/color]
between[color=blue]
> 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 TblTrainingComp leted table would have these fields:
> TrainingComplet edID
> EmployeeID
> DateCompleted
> TopicID[/color]

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 TblTrainingComp leted VALUES (1, 2, "6/23/2003", 5)
INSERT INTO TblTrainingComp leted VALUES (2, 2, "6/23/2003", 5)

[Marcy]

Re: Implementing A Junction Table

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" <AllenBrowne@Se eSig.Invalid> wrote in message
news:403470c7$0 $22519$5a62ac22 @freenews.iinet .net.au...[color=blue]
> 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 TblTrainingComp leted table would have these fields:
> TrainingComplet edID
> EmployeeID
> DateCompleted
> TopicID
>
> Now you can create a query into TblEmployeeID, TlJob, TblRequiredJobT opic,
> 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 TblTrainingComp leted 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" <mthomas@earthl ink.net> wrote in message
> news:e0_Yb.1141 1$hm4.7886@news read3.news.atl. earthlink.net.. .[color=green]
> > I am trying to build a database to keep track of training topics completed[/color]
> by[color=green]
> > people in my department. Our department has a set of 37 training topics.[/color]
> There[color=green]
> > are 7 job classifications in the department. Each job classification has a[/color]
> group[color=green]
> > of required training topics. Some of the training topics are required by[/color]
> more[color=green]
> > than one job classification. I have the following tables:
> > TblJob
> > JobID
> > JobDescription
> >
> > TblTopic
> > TopicID
> > Subject
> >
> > TblRequiredJobT opic
> > RequiredJobTopi cID
> > JobID
> > TopicID
> >
> > TblTrainingComp leted
> > TrainingComplet edID
> > EmployeeID
> > DateCompleted
> > RequiredJobTopi cID
> >
> > I want to use a continuous form to add the records to[/color]
> TblTrainingComp leted. I'm[color=green]
> > stuck on how to enter the RequiredJobTopi cID field. It seems to me that I[/color]
> need[color=green]
> > two comboboxes, one to define JobID and the other to define TopicID, so as[/color]
> to[color=green]
> > define RequiredJobTopi cID from the junction table, TblRequiredJobT opic. In[/color]
> a[color=green]
> > 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[/color]
> me.[color=green]
> >
> > Marcy[/color]
>
>[/color]

[Rolls]

Re: Implementing A Junction Table

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]

Re: Implementing A Junction Table

Marcy wrote:
[color=blue]
> I'm sorry but I don't follow whay you are saying. Are you suggesting replacing
> RequiredJobTopi cID in TblTrainingComp leted 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.[/color]

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 tblTrainingComp leted to exist in
tblRequiredJobT opic.

--
Bas Cost Budde

http://www.heuveltop.org/BasCB

but the domain is nl

[Allen Browne]

Re: Implementing A Junction Table

If TblTrainingComp leted 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" <mthomas@earthl ink.net> wrote in message
news:9u6Zb.1207 2$hm4.2452@news read3.news.atl. earthlink.net.. .[color=blue]
> 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[/color]
required[color=blue]
> by his jib classification.
>
> Marcy
>
>
> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
> news:403470c7$0 $22519$5a62ac22 @freenews.iinet .net.au...[color=green]
> > Marcy, the first 3 tables you have a great, but there seems to be[/color][/color]
something[color=blue][color=green]
> > missing.
> >
> > Presumably you also have an Employee table. What is the relationship[/color][/color]
between[color=blue][color=green]
> > Employee and Job? Does an employee only hold one job at a time? Or can[/color][/color]
an[color=blue][color=green]
> > employee have multiple jobs (e.g. part time in different jobs, or[/color][/color]
holding[color=blue][color=green]
> > multiple responsibilties at once). If multiple, you have a many-to-many
> > relationship between Employee and Job, so you need another junction[/color][/color]
table:[color=blue][color=green]
> > TblEmployeeJob
> > EmployeeID
> > JobID
> > StartDate
> >
> > The TblTrainingComp leted table would have these fields:
> > TrainingComplet edID
> > EmployeeID
> > DateCompleted
> > TopicID
> >
> > Now you can create a query into TblEmployeeID, TlJob,[/color][/color]
TblRequiredJobT opic,[color=blue][color=green]
> > to get a list of the topics an employee should have done. Use DISTINCT[/color][/color]
to[color=blue][color=green]
> > get only one listing of a topic where duplicated. Save this query. Use[/color][/color]
the[color=blue][color=green]
> > Unmatched query wizard to get records in TblTrainingComp leted that are[/color][/color]
not[color=blue][color=green]
> > in this query.
> >
> >
> > "Marcy" <mthomas@earthl ink.net> wrote in message
> > news:e0_Yb.1141 1$hm4.7886@news read3.news.atl. earthlink.net.. .[color=darkred]
> > > I am trying to build a database to keep track of training topics[/color][/color][/color]
completed[color=blue][color=green]
> > by[color=darkred]
> > > people in my department. Our department has a set of 37 training[/color][/color][/color]
topics.[color=blue][color=green]
> > There[color=darkred]
> > > are 7 job classifications in the department. Each job classification[/color][/color][/color]
has a[color=blue][color=green]
> > group[color=darkred]
> > > of required training topics. Some of the training topics are required[/color][/color][/color]
by[color=blue][color=green]
> > more[color=darkred]
> > > than one job classification. I have the following tables:
> > > TblJob
> > > JobID
> > > JobDescription
> > >
> > > TblTopic
> > > TopicID
> > > Subject
> > >
> > > TblRequiredJobT opic
> > > RequiredJobTopi cID
> > > JobID
> > > TopicID
> > >
> > > TblTrainingComp leted
> > > TrainingComplet edID
> > > EmployeeID
> > > DateCompleted
> > > RequiredJobTopi cID
> > >
> > > I want to use a continuous form to add the records to[/color]
> > TblTrainingComp leted. I'm[color=darkred]
> > > stuck on how to enter the RequiredJobTopi cID field. It seems to me[/color][/color][/color]
that I[color=blue][color=green]
> > need[color=darkred]
> > > two comboboxes, one to define JobID and the other to define TopicID,[/color][/color][/color]
so as[color=blue][color=green]
> > to[color=darkred]
> > > define RequiredJobTopi cID from the junction table,[/color][/color][/color]
TblRequiredJobT opic. In[color=blue][color=green]
> > a[color=darkred]
> > > continuous form they both need to be bound to hold their value and I[/color][/color][/color]
can't[color=blue][color=green][color=darkred]
> > > figure out how to implement them. I appreciate any help anyone can[/color][/color][/color]
give[color=blue][color=green]
> > me.[color=darkred]
> > >
> > > Marcy[/color][/color][/color]