Basic help with new form

[mjc]

Here's my question, stripped down:

I have an organization table.
I have an employee table (multiple orgs each have individual employees).
I have a training session table (employees attend training sessions.)

However, employees will on occassion attend multiple training sessions. So
I want to set up some way to create a new training session, and add multiple
employees to that session.

Bonus Question:
Since we deal with quite a number of people, would there be a way to first
create a listbox for organization, then a listbox for employee, to make
finding that person easier?

 

[tina]

However, employees will on occassion attend multiple training sessions.
So

I want to set up some way to create a new training session, and add multiple
employees to that session.

what you have is a many-to-many relationship between employees and training
sessions: one employee may attend many training sessions, AND one training
session may be attended by many employees. in Access, you model a
many-to-many relationship with a "join" or "linking" table, which acts as
the "many" side of a one-to-many relationship between employees and training
sessions.

tblEmployees
EmpID (primary key)
FirstName
LastName

tblTrainingSessions
SessionID (pk)
Subject
SessionDate

tblEmployeeTraining
EmpSessionID (pk)
SessionID (foreign key from tblTrainingSessions)
EmpID (fk from tblEmployees)

relationships would be
tblTrainingSessions.SessionID 1:n tblEmployeeTraining.SessionID
tblEmployees.EmpID 1:n tblEmployeeTraining.EmpID

hth

 

[tina]

Bonus Question:

Since we deal with quite a number of people, would there be a way to first
create a listbox for organization, then a listbox for employee, to make
finding that person easier?

at the form level, yes. the following assumes that each record in
tblEmployees has a foreign key field which links the employee to a specific
organization record in tblOrganizations. i'll call that field EmpOrgID.
create the two listboxes, based on tblOrganizations and tblEmployees,
respectively. i'll call the controls lstOrgs and lstEmps. in the RowSource
property for lstEmps, set criteria on field EmpOrgID as

Forms!FormName!lstOrgs

in the AfterUpdate event procedure of lstOrgs, add the following code, as

Me!lstEmps.Requery

each time you select a different organization in lstOrgs, then lstEmps will
be requeried to show only the employees who belong to that organization.

hth

 

[mjc]

Thanks everyone. That was very helpful. I dug around a bit, and for the
"bonus question" ended up linking it to EmployeeID, and having it show
[FirstName] & " " & [LastName] & " - " [Organization]. Very cool.