SNicholls
You need to create a joiner / intermediary / profile table. I like the term "profile", but the other names are more commeon.
Some assumptions...
ProjectTbl
ProjectID - primary key
ProjectName
+ other details on the project
EmployeeTbl
EmployeeID - primary key
EmployeeLN
EmployeeFN
+ other details pertaining to the employee
Okay, here is the meat
ProjectRoleTbl
ProjectID - foreign key to the primary key on project table
EmployeeID - foreign key to the primary key on employee table
Role - To be discussed
The primary key, to prevent an employee being assigned to the project more than once is...
ProjectID + EmployeeID
If you need help setting two fields as the primary key...
In table design mode, select the first field.
Then right click on the second feild and select Primary key from the list. (There are two other ways in the table design mode that also work.)
Role field deserve a bit of discussion
The simplest approach would be to use a text field. Using just the description works just fine in many cases. But let's pursue further...
What if you want to find a list of all project managers. Your query would be do-able for this but you would have to know the specific title that needs to be selected. Then there are the typo's, and variation in the name.
Perhaps a better approach would be to have a Role table. This will force the person entering the data to select a predefined role - no typo's, and this table can now be easily used to select all employees currently work at what capacity for the projects.
Alright, you may have another tweak for this. Even though a person fits under as one title, say technical lead. But they want to use a different title for political reasons per say, then add a second field in the Project Role table for this purpose - purely descriptive.
RoleTbl
RoleCode - Primary key
RoleDesc - description for role
Hint: You probably want to display roles in terms of management level. You can build this in to the RoleCode (or create a sort field).
[tt]
01_PMAN Project Manager
02_PSUP Project Supervisor
03_PTKL Technical Lead
99_PEON Most talented hard working and smartest of the bunch ;-)
[/tt]
One more issue to ponder.
For large projects, positions may change. People come and go. Or a person changes their role within the project. Now suppose you need to find historical information -- who worked out the technical problem last year on this issue. It is not the same person now working in the position.
You can either create a historical table to track this information, or modify the existing table.
ProjectRoleTbl
ProjectID - FK key to the project table
EmployeeID - FK to the employee table
RoleCode - FK to the role table
RoleDesc - purely a descriptive role of the employee
Active - yes / no
StartDate - date field
EndDate - date field
Comment - memo field
In order for this design to work, the primary key for this has to change to...
ProjectID + EmployeeID + StartDate
You can now track who worked on what project, in what capacties and when.
Now perhaps you can see why I like calling these joiner tables as profiles....
Presentation
You may want to create two contineous subforms based on this table. (Remember the RoleCode have a built in sort function?? Works great here)
One subform is added to the Project form and displays employees on the project. Here you hide the ProjectID on the subform based on the ProjectRole table, and use ProjectID to link to the ProjectID for the main form.
The second subform is basically the same, but now you hide the EmployeeID field and use it to link to the parent Employee table. When you bring up the employee form, you can see what projects and in what capacity (and when) the employee is working at.
Hopefully this helps where you can take the level of detail you need and apply it to your situation.
Richard
