Junction table question

[Bruce]

Thanks to JulieD and others I have supplemented my reading
on Access and have begun to organize company information.
I have learned I need a juction table to accomplish the
following: I need to record training for employees.
Training could be a one-day course, or it could be a five
minute unscheduled session at a supervisor's discretion.
The training will be logged by entering the description of
the session and the attendees. I will later need to list
the training that each employee has received.
I have an employee table (tblEmployee), a training session
table (tblSession), and a junction table (tblAttendance),
thus:
tblEmployee
* EmployeeID (number)
Employee Name
Other employee info. fields
tblSession
* SessionID (autonumber)
Session (text description of session)
Date
Other session information fields
tblAttendance
EmployeeID (PK from tblEmployee)
SessionID (PK from tblSession)

I have created a one to many relationship from tblEmployee
and another from tblSession, both to tblAttendance. Now I
think I need to have a form to enter the session name,
date, etc., and a subform to enter.
Every explanation I have seen of junction tables seems to
suggest that all it needs to contain are the foreign keys
(and maybe a PK for the junction table?). So my question
is, if I have a form to record a training session (bound
to tblSession), I need a subform to record employees.
Here is where I am having trouble. To what table, query,
etc. is the subform bound? It can't be to the junction
table alone, since that would involve my knowing each
employee's ID number in order to record their attendance.
Yet the junction table is where I need to bring together
the employees and the training session. What am I
misssing? While I'm at it, I want to select employee
names from a list that will need to be concatenated as
[last]&", "&[first]. I can create a query (qryRoster) to
concatenate, but how do I tie that in to the subform?
I feel like I'm getting closer. I finally realized I was
stuck in flat database thinking, and that the junction
table would contain, say, five individual records for a
training session for five employees. I kept trying to
figure out how to create a single record in the juction
table for each training session. However, I am still
stuck trying to record employee information. What am I
missing?

 

[Roger Carlson]

Generally, the subform is based on a JOIN of the junction table and one of
the other tables (the one not being used as the record source for the
mainform).

On my website is a small sample database called
"ImplementingM2MRelationship", which illustrates this.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Thanks to JulieD and others I have supplemented my reading
on Access and have begun to organize company information.
I have learned I need a juction table to accomplish the
following: I need to record training for employees.
Training could be a one-day course, or it could be a five
minute unscheduled session at a supervisor's discretion.
The training will be logged by entering the description of
the session and the attendees. I will later need to list
the training that each employee has received.
I have an employee table (tblEmployee), a training session
table (tblSession), and a junction table (tblAttendance),
thus:
tblEmployee
* EmployeeID (number)
Employee Name
Other employee info. fields
tblSession
* SessionID (autonumber)
Session (text description of session)
Date
Other session information fields
tblAttendance
EmployeeID (PK from tblEmployee)
SessionID (PK from tblSession)

I have created a one to many relationship from tblEmployee
and another from tblSession, both to tblAttendance. Now I
think I need to have a form to enter the session name,
date, etc., and a subform to enter.
Every explanation I have seen of junction tables seems to
suggest that all it needs to contain are the foreign keys
(and maybe a PK for the junction table?). So my question
is, if I have a form to record a training session (bound
to tblSession), I need a subform to record employees.
Here is where I am having trouble. To what table, query,
etc. is the subform bound? It can't be to the junction
table alone, since that would involve my knowing each
employee's ID number in order to record their attendance.
Yet the junction table is where I need to bring together
the employees and the training session. What am I
misssing? While I'm at it, I want to select employee
names from a list that will need to be concatenated as
[last]&", "&[first]. I can create a query (qryRoster) to
concatenate, but how do I tie that in to the subform?
I feel like I'm getting closer. I finally realized I was
stuck in flat database thinking, and that the junction
table would contain, say, five individual records for a
training session for five employees. I kept trying to
figure out how to create a single record in the juction
table for each training session. However, I am still
stuck trying to record employee information. What am I
missing?

 

[Bruce]

I checked out your sample DB. Thank you. Now I wish I
could figure out why I can't get mine to behave the same
way. I put together a query (qryEnrollment) containing
all of the fields except PK from the junction table
(tblEnrollment) and all of the fields except PK from
the "static" table (tblEmployee). Then I used the forms
wizard to construct a form/subform. The main form is
based on tblSession (the "active" table), and the subform
is based on qryEnrollment. Seems OK so far. The subform,
by the way, contains only the date, First Name, and Last
Name. So I enter the session information in the main
form, and go to the subform to enter the attendees.
However, by doing so I am adding records to the "static"
tble (tblEmployee) as well as to the junction table.
Again, EmployeeID is the PK in tblEmployee, and is a FK in
the junction table. SessionID is the PK in the main
form's record source (tblSession) and is a FK in the
junction table. One-to-many relationship from each PK to
its FK. No PKs are in the query on which the subform is
based.
I am still missing something. I do not want to add
records to tblEmployees, only to tblSession and to the
junction table.
By the way, same result whether I use the wizard or not.

-----Original Message-----
Generally, the subform is based on a JOIN of the junction table and one of
the other tables (the one not being used as the record source for the
mainform).

On my website is a small sample database called
"ImplementingM2MRelationship", which illustrates this.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Thanks to JulieD and others I have supplemented my reading
on Access and have begun to organize company information.
I have learned I need a juction table to accomplish the
following: I need to record training for employees.
Training could be a one-day course, or it could be a five
minute unscheduled session at a supervisor's discretion.
The training will be logged by entering the description of
the session and the attendees. I will later need to list
the training that each employee has received.
I have an employee table (tblEmployee), a training session
table (tblSession), and a junction table (tblAttendance),
thus:
tblEmployee
* EmployeeID (number)
Employee Name
Other employee info. fields
tblSession
* SessionID (autonumber)
Session (text description of session)
Date
Other session information fields
tblAttendance
EmployeeID (PK from tblEmployee)
SessionID (PK from tblSession)

I have created a one to many relationship from tblEmployee
and another from tblSession, both to tblAttendance. Now I
think I need to have a form to enter the session name,
date, etc., and a subform to enter.
Every explanation I have seen of junction tables seems to
suggest that all it needs to contain are the foreign keys
(and maybe a PK for the junction table?). So my question
is, if I have a form to record a training session (bound
to tblSession), I need a subform to record employees.
Here is where I am having trouble. To what table, query,
etc. is the subform bound? It can't be to the junction
table alone, since that would involve my knowing each
employee's ID number in order to record their attendance.
Yet the junction table is where I need to bring together
the employees and the training session. What am I
misssing? While I'm at it, I want to select employee
names from a list that will need to be concatenated as
[last]&", "&[first]. I can create a query (qryRoster) to
concatenate, but how do I tie that in to the subform?
I feel like I'm getting closer. I finally realized I was
stuck in flat database thinking, and that the junction
table would contain, say, five individual records for a
training session for five employees. I kept trying to
figure out how to create a single record in the juction
table for each training session. However, I am still
stuck trying to record employee information. What am I
missing?

.