Access 2003 Lookup Lists

[tlang]

I am trying to create an Access Database that will track employees training
by keeping track of classes they have already taken and keeping track of
classes they are scheduled to, or would like to, take in the future. My plan
is to have a table for employees, a table for offices (my organization has
several), a table for classes, and a table for schools. I would like to
create a form that has a dropdown list that will allow someone to select one
or several of the classes listed in the dropdown list, including a choice for
"Other (please specify below)." A text box would be located beneath the
dropdown list to allow employees to enter the names of clases not included
int he dropdown list. The problem is that I have found no indication on
whether it is possible to have a dropdown list that can allow a user to
select more than one item in the list. Can this be done in Access 2003, or
will I have to rethink my strategy, perhaps by allowing a text box requesting
a Course #, a Course Title, and a School Name?

 

[Allen Browne]

You proposed these tables:
- tblOffice: one record for each office location, with OfficeID primary key
- tblEmployee: one record for each person, with EmployeeID primary key
- tblSchool: one record for each education provider, with SchoolID primary
key
- tblClass: one record for each class.

The first 3 are good. The trouble with the 3rd one is that one unit (e.g.
"Introduction to computing") could be offered many times (over the years),
or by many schools. You therefore need:
- tblUnit one record for each unit of stude, with UnitID primary key

Then tblClass will have fields like this:
ClassID AutoNumber primary key
UnitID Relates to tblUnit.UnitID (i.e. what this class is
studying)
SchoolID Relates to tblSchool.SchoolID (who provides this class)
StartDate Date/Time when this class starts

Now you need a table to record who is in a class (tblEnrol):
EnrolID AutoNumber primary key
ClassID Relates to ClassID.ClassID (which class this person is
in.)
EmployeeID Relates to Employee.EmployeeID (who is in this class.)

The interface will be a main form bound to tblClass, with combos for
choosing what the unit is and which school provides this class. It will have
a subform bound to tblEnrol. The subform will be in continuous view, so you
can enter as many rows as there are people in the class. You can use a combo
to select the employee, and each employee has their own record (separate
row) in the subform.

If you wish, you can also create a main form bound to tblEmployee, and
include a subform to show which classes they have done.