My problem is that I
have 3 different types of employees. Each will requie fields that the
other two do not. Do I create 3 different employee tables, one for
each or do I place all 3 types of employees into one table and leave
the fields empty?
You actually need four tables:
Employees( *EmpID, FullName, OtherStuffCommonToAllEmployees)
PartTimers( *EmpID+, MaxSessions, SessionsInHand, etc)
FullTimers( *EmpID+, OverTimeRate, ContractRevsions, etc)
Temps( *EmpID+, DateWhenFullRightsGranted, etc)
All employees get an Employees record, and the EmpID from this table
supports all transactions for them. They also get a record from exactly
one of the other tables, obviously taking their EmpID with them. The +
sign means that the EmpID is a foreign key referencing Employees, so that
you can't have a FullTimer that isn't also an Employee, and you can't
delete his Employee record until you've deleted the FullTimer record too.
Being PK to PK, it's a one-to-one relationship; this is one of the few
genuine reasons for such a thing.
Unfortunately, Access cannot guarantee the rule that you can't create a
FullTimer record at the same time as a PartTimer record, but as long as
you can control all data access via forms, then a little bit of code will
do it for you. If you have the chance to move up to a real DBMS (ahem!)
then you can use update triggers to enforce it.
This is quite a common technique known as Subtyping.
Hope it helps
Tim F