What you have is a "many to many" relationship. A given person can be assigned to multiple projects and a project can have multiple people assigned to it.
Set up three tables like this:
Personnel::PersonnelID = Roles::PersonnelID
Project::ProjectID = Roles::ProjectID
Add a "role" field in Roles to identify the role that a person is assigned for that project. If a person has multiple roles for the same project, make one such record for each role in the Roles table, but specify a different role. Alternatively, a check box group formatted field in the Roles table could be used, but this can be a less flexible approach for certain reports. A portal to Roles on the personnel table can list all their assigned roles for all their assigned projects. (A portal filter could limit it to one specified project at a time.) A portal to Roles on the project layout could list all Roles for all personnel assigned to the current project record.
Here's a demo file on many to many relationships you may want to examine. If you use the one record for each role setup that I am suggesting, DO NOT try to use the methods in this demo file for eliminating duplicate join records as you will need such records to list the multiple roles asigned to one person.
Thank you!! I have started to set up the database like you suggested. Someone on another forum suggested this set-up:
Join table: Role_Resource
Now one Project can have any number of Roles, and any Resource can fill any number of Roles.
What's your thoughts on this variation? I'm hoping to do this once, and not having to redo it so I want to determine the 'best practice' - very greatful for your insight!!
The only variation that I see is in the names used. The structure looks identical to me except for the addition of a primary key for the Role table--which is not a bad idea, just not something actually used yet in what's set up here. (I often leave out primary key fields for utility tables where it isn't obvious that I will need a primary key. I can get away with that because adding in a primary key after the fact is a very simple thing to do.)
Thank you for your continued support. You can tell I'm new to this, huh! :D I do have more questions; I read it as if there were 4 tables in this last setup, the 4th table being the 'Roles_Resources' table, and in your scenario I saw 3 tables. I sketched out my interpretation of each layout like this:
Where am I loosing the thread? Still not sure how to fully utilize this in the layout. Up till now I've used one giant flat database, but I know it's time to 'grow up'! :)
Duh! I'm just not seeing the obvious! can't count today!
Can't say off hand that my approach is better or worse than this other suggested approach. The difference is that you have a single join record linking a particular resource to a particular project with a related table used to list all the project roles for that resource.
Mine handles that issue by setting up multiple "role" records for the same person when they have more than one role for that project. The layout design will be a bit different for this second approach as you can't put a portal to roles inside a portal to the join table, but that doesn't make it a worse option here, it'd just need to be handled in a different fashion.