Mass Data Entry for a Junction Table

[Steve]

On my Junction table we track the employee and what education
offerings they have attended. We have found that we have multiple
employees attending the same offerings on the same day. (makes sense,
if a class is offered one day you will about 20-30 people in that
class) Is there any way to simplify the data entry process.

Right now on the form the person doing data entry selects the
employee, the education offering and the date. This has to be done for
each attendee.

What would be great would be to create a form that allows you to
select a course and a date, and then select multiple employees. (A
seperate record would need to be created for each employee on the
junction table.)

Does anyone know if this is possible?

 

[John Spencer]

It should be possible using a form with a subform.

The main form would need controls to select a class and enter a date.
Link the Form to the subform on the date and course controls and the relevant
fields in the junction table. The subform would have a combobox to select the
employee from in each record.

I am assuming that your junction table has employeeId, ClassID, and
DateofClass fields. Or if a class and date are stored in one record then you
only need to bind to class, since the date is always the same for a class.

I might have a structure more like:

tblCourses (Things to be taught)
CourseID
CourseName
CourseProvider
....

tblClass (Details on a particular class session for a particular course)
ClassID
CourseID
ClassDate
....

tblEmployeeTraining (Junction table)
EmployeeID
ClassID

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Steve]

I have done a form like this many times! You need a form/subform. You
display the the class and date in the main form. In the subform you display
a list of all employees and a checkbox next to each. ClassID and ClassDate
are hidden fields in the subform. You need code in the Afterupdate event of
the checkbox to assign ClassID and ClassDate:
Me!ClassID = Me.Parent!ClassID
Me!ClassDate = Me.Parent!ClassDate
When it's set up correctly, all you need to do is go down the list and
check-off attendees. If the number of employees is small, you can display
all the employees at once. If the number of employees is large, you can but
buttons in the subform to display groups of employees such as:
A-F
G-L
M-R
S-Z

Steve
(e-mail address removed)

 

[Steve]

Tahnk you both Steve and John I will try these, I knew it had to be
possible!

Steve

 

[Steve]

Tahnk you both Steve and John I will try these, I knew it had to be
possible!

Steve

Again thank you both, I am struggling with tring to get the list set
up in my subform. Do you use the Junction as the sourse for both the
Form and the Sub Form? I would assume that I want to use the table
that holds my Staff IDs?

 

[Steve]

Again thank you both, I am struggling with tring to get the list set
up in my subform. Do you use the Junction as the sourse for both the
Form and the Sub Form? I would assume that I want to use the table
that holds my Staff IDs?

 

[John Spencer]

NO, you use the junction table as the source for the subform only.

You can have NO record source for the main table and just have a combobox that
lists the courses (assuming you have a table of courses) and a textbox that
has the course date. You fill (select) values for the combobox and textbox
and LINK the relevant fields in the junction table to the CONTROLS on the main
form.

Alternative is that you have a table that has all the courses and course dates
and you use that as the record source for the main form. If you use the table
(or better a query of the table) as a record source, then you can link the
fields from the course list (master) to the appropriate fields in the
sub-form's source.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Steve]

If you see this - I will get back later with a detailed description on how
to set it up. It may be tomorrow so keep checking back.

To start, you'll need the following tables:
TblEmployee
EmployeeID
FirstName
LastName
etc

TblCourse
CourseID
CourseName
etc

TblClass
ClassID
ClassDate
CourseID
InstructorID (You need to set this up according to who teaches your classes.
Could be ab employeeID)

TblClassAttendee
ClassAtendeeID
ClassID
EmployeeID

Steve

Tahnk you both Steve and John I will try these, I knew it had to be
possible!

Steve

Again thank you both, I am struggling with tring to get the list set
up in my subform. Do you use the Junction as the sourse for both the
Form and the Sub Form? I would assume that I want to use the table
that holds my Staff IDs?

 

[Steve]

Add a field named Selected to TblClassAttendee.

1. Create a query named QryFrmClass based on TblClass. Sort ascending on
ClassDate.
2. Create a form named FrmClass. Make QryFrmClass the recordsource of
FrmClass. Nmae each textbox on the form the same as the field name in
QryFrmClass.
3. Create a query named QryClassAttendee based on TblClassAttendee. Set
the creiteria for ClassID to Forms!FrmClass!ClassID
4. Create a query that includes TblEmployee and QryClassAttendee. Join
EmployeeID in TblEmployee to EmployeeID in QryClassAttendee. Click on the
join line and make it a type 2 join. Name the query QrySFrmClassAttendee.
5. Create a continuous form named SFrmClassAttendee with a recordsource
of QrySFrmClassAttendee. Include the fields FirstName and LastName from
TblEmployee, Selected, ClassID and EmployeeID from TblClassAttendance and
EmployeeID from TblEmployee. Lock and disable the FirstName and LastName
fields. Make ClassID and EmployeeID from TblClassAttendance and EmployeeID
from TblEmployee not Visible. Name EmployeeID from TblEmployee
"EmployeeIDfromTblEmployee".
6. Put the following code in the AfterUpdate event of the Selected
checkbox:

If Not IsNull(Me!Selected) Then
Me!ClassID = Me.Parent!ClassID
Me!EmployeeID = Me!EmployeeIDfromTblEmployee
Else
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
Me.Requery
End If

7. Make SFrmClassAttendee a subform on FrmClass. MAKE THE LINKMASTER AND
LINKCHILD PROPERTIES BLANK!!

When you open FrmClass, you will be able to navigate to any class you
previously entered. In the subform you will see a list of all employees.
Check the ones that attended the class. Each time you check, you will add an
attendance record to TblClassAttendance. If you uncheck an employee, you
will delete his record from TblClassAttendee that he attended the class.

Steve
(e-mail address removed)

Tahnk you both Steve and John I will try these, I knew it had to be
possible!

Steve

Again thank you both, I am struggling with tring to get the list set
up in my subform. Do you use the Junction as the sourse for both the
Form and the Sub Form? I would assume that I want to use the table
that holds my Staff IDs?