Source data for many fields in a table from one field in another t

[Dave]

I am building a database in Access 97 (this is our company's standard). I
have a table that contains data about a process. The main table needs to
source data from the "Employees" table (a list of employees and their
details). However, there are several fields in the main table that need to
source this information, such as "Originator" (one only), "Contributors"
(could be many), "Signed Off - Strategist" (one only) and "Signed Off -
Manager" (one only).

Typically, I would have an "EmployeeID" field in both tables and would link
them with a one-to-many relationship between the main table and the Employees
table. However, as several fields in the main table are needing to link to
the EmployeeID field, I am not sure about the best way to do this (with
respect to good database design, relationships, field names, etc).

I have been unable to find any information that deals with this situation,
so any help or advice would be greatly appreciated.

Thanks,

Dave.

 

[Steve]

Since there could be many contributors, you meed a separate table for
contributors. Since there is only one Originator, one Strategist and one
Manager, these fields are OK on your process table.

TblProcess
ProcessID
ProcessDesc
OriginatorEmployeeID
StrategistEmployeeID
ManagerEmployeeID
<Other data fields about the process>

TblEmployee
EmployeeID
<Other detail fields regarding employee>

TblProcessContributor
ProcessContributorID
ProcessID
ContributorEmployeeID

You then need relationships:
EmployeeID ---> OriginatorEmployeeID
EmployeeID ---> StrategistEmployeeID
EmployeeID ---> ManagerEmployeeID
EmployeeID ---> ContributorEmployeeID

Steve

 

[Dave]

Thanks for the assistance, Steve. That clarifies it for me.

Dave.