Linking Tables

[dvlander]

Suppose we have a database of projects and project assignments. The person
performing a project assignment can be either an employee or a contractor. I
have already created a separate employee table and a separate contractor
table. If I create a Project Assignment table, the Assignee obviously has to
either an employee or a contractor. Do I need to have separate fields in the
Assignment Table for EmployeeID and ContractorID and link to those two
tables? --- or, is there a way to have a single Assignee field somehow linked
to the two tables? If we have separate fields for each, one of them will be
blank for each record depending on whether an employee or contractor does the
work? Thanks.

 

[Sninkle]

Why not have one table listing Employees and Contractors which a type field
to specify if it is Employee or Contractor. Then you only need to connect
one table to the Project Assignment table.

 

[dvlander]

Great suggestion - However, we track certain things for Contractors that we
would not for employees (i.e., Vendor Name, Bill Rate etc.). I guess those
fields could be blank for the employee records.

Thanks you much - I appreciate it. Dale

 

[Jeff Boyce]

If you have "folks who could perform a project assignment", and some folks
are Employees and others are Contractors, another approach might be to one
table to "register" folks, and two separate tables to store details (one for
Employees' details, one for Contractors' details). This way, you use the ID
from the "folks who could..." table to connect to the projects on assignment
(as suggested earlier, one table).

Regards

Jeff Boyce
Microsoft Office/Access MVP