lost in a many to many to many relationship 1

[SNicholls]

My main table is a list of projects completed. The primary key is ProjectName. Each ProjectName has Staff (more than one). Each Staff has a specific Role in this project; the role may be different for a different project. In addition, each Staff has a yes/no field (does staff have a resume on file?) where the answer will always be the same for this Staff person.

--One ProjectName can have many Staff. Staff can work on many projects.
--Staff can have only one role in a certain project but staff could have a different role in a different project.

I keep getting tripped up over how many tables I need, which fields appear in both (all) tables, which is the primary key of the same name (or where I can use an autonumber), etc.

I'm building a form to allow dataentry for all these fields. I presume I need one (or more) subform(s) in the form, but I don't know which tables have the forms that become the subforms.

I appreciate your help.

 

[SkipVought]

Hi,

While I'm thinking about this, check this out --

http://www.abcdataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

This might basically work. Consider Staff Role as a PROJECT DETAIL element rather than a Staff element
[tt]
------- ------- --------
Project ProjDet Staff
------- ------- --------
ProjID -<ProjID
PDID
StfID >-StfID
StfRole
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[willir]

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

 

[SNicholls]

Skip, I recognized that hyperlinked text from Access 2002 Desktop Developers Handbook; thanks.
Willir, I appreciate the level of detail. I needed that and more, but I finally got it working.
SNicholls