Another newbie question here. In my publication database, I set up a master table (tblProjectOverview) that will be populated
with data from other users to capture the basic information for each project: Project Name, if it needs funds, if it will be
printed, and the names of people working on it. Many of the projects won't need funds and won't be printed, but for those
that will, I need to track other data. I've already gotten help restructuring the funding tracking part of it (thanks
ssanfu!). Now I need to tackle the personnel part of it.
I have set up Project Name in tblProjectOverview as a primary key, as there will be no 2 projects the same. I need to be able
to link other data to the project's name, including a whole checklist of items that we will fill out if the the project
actually needs to be printed.
I've set up a separate Personnel table, with names and contact info. The person's name is set up as [Last, First] so it can
serve as the primary key. Then I used tblPersonnel as a lookup table in tblProjectOverview, so users can just pick the team
members from a list.
My biggest problem is that I need to be able to capture which projects each person is working on. Each project will have a
Lead, and some projects will have up to two more team members. A staff member may be in any of the 3 positions, but will not
be in more than 1 position.
How do I view all the projects an individual staff member is working on, regardless of their assigned position within that
project? In my overview I have the fields Lead, Member1, and Member 2. Perhaps this is part of the problem.
I've attached a zipped sample file (hope it works ok, haven't zipped anything on my new computer until now!). In the sample
the financial structure works, and I have added the overview and other tables I'm using to test the mechanics.
As with my earlier question I'm betting it's something simple . . . I appreciate your patience!
(25.4 KB, 10 views)
Reply With Quote
03-07-2011, 11:44 AM
Windows 7 64bit Access 2010 64bit
Join Date Oct 2010
Location Southern USA
In my overview I have the fields Lead, Member1, and Member 2. Perhaps this is part of the problem.
yep, it is. In order for a field to be appropriate for a table, the field must be a fact about the primary key... a fact that
no value in the primary key field could ever have more than one of. A project can have more than one person, so you cannot
put persons in there. The Projects table is only for facts that a project can only have one of.
Additionally... not only can a project have more than one person, but a person can have more than one project. This is a
many-to-many relationship, so a junction table is needed...
If I were you, I would make a new table consisting of the PK's from the Personnel table and the Projects table... the
combination of these could serve as PK in this new table. Add a field that either is Yes/No IsTeamLead, or add a Position
field. Relate the new table to Personnel and to Projects.