Access 2000 Subdatasheet

[Guest]

I am creating a training record for all employees at work. One of the tables
containes all classes available for the upcoming year. The other table is
each individual employee. I want to have a subdata sheet linking the employee
to the classes they are signed up for. The problem is more than one employee
can take the same class.

I attempted to have a seperate colum for each emplyee's number in the class
table. I can not get it to relate to the employee number in the employee
table unless ALL of the class table colums contain the same number.

Is there a way to have the employee table link to the class table if one of
the many tables matches?

 

[tina]

recommend that you don't use subdatasheets in your tables; see
http://home.att.net/~california.db/tips.html#aTip6 for details. data
entry/review should be done in forms, not tables, so subdatasheets are
unnecessary.

I attempted to have a seperate colum for each emplyee's number in the class
table.

such a setup breaks data normalization rules, which you should avoid doing
except in rare instances.

one employee may sign up for many classes, and one class may have many
employees. so you have a many-to-many (n:n) relationship between tblClasses
and tblEmployees. in Access, you can't model an n:n relationship directly,
so you use a third table to "link" or "join" the records in the other two
tables, as

tblClasses (parent table)
ClassID (primary key)
ClassName
(other fields that describe a class)

tblEmployees (parent table)
EmployeeID (primary key)
FirstName
LastName
(other fields that describe an employee)

tblClassEmployees (child "linking" table)
ClassEmpID (primary key)
ClassID (foreign key from tblClasses)
EmployeeID (foreign key from tblEmployees)
(note: if you want, you can get rid of the ClassEmpID primary key field,
and instead use the two foreign key fields as a combination primary key for
this table.)

the table relationships are:
tblClasses.ClassID 1:n tblClassEmployees.ClassID
tblEmployees.EmployeeID 1:n tblClassEmployees.EmployeeID

to model the relationships for efficient data entry, you can use a standard
mainform/subform setup. create a form bound to tblClassEmployees, i'll call
it frmClassEmployees. bind the EmployeeID field to a combo box. set the
RowSource of the combo box to tblEmployees, so you'll be able to choose an
employee from the combo box droplist. create another form bound to
tblClasses, i'll call it frmClasses; this is the main form. add
frmClassEmployees to frmClasses, as a subform.

with the above setup, you can go to a class record in the main form, and
list all the employees who signed up for that class in the subform - one
subform record per employee name.

hth

 

[John Vinson]

I am creating a training record for all employees at work. One of the tables
containes all classes available for the upcoming year. The other table is
each individual employee. I want to have a subdata sheet linking the employee
to the classes they are signed up for. The problem is more than one employee
can take the same class.

I attempted to have a seperate colum for each emplyee's number in the class
table. I can not get it to relate to the employee number in the employee
table unless ALL of the class table colums contain the same number.

Is there a way to have the employee table link to the class table if one of
the many tables matches?

Yes, by using a *THIRD* table - Enrollment. This table should have two
fields - the EmployeeID as a link to the Employee table, and the
ClassID as a link to the class table. See the Northwind Sample
database Orders form for a closely related example (an Order can
contain multiple Items, and each Item can be in multiple Orders, just
as a Class can contain multiple employees and each employee can take
multiple classes).

John W. Vinson[MVP]