Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    wes is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    29

    Identify projects by personnel from 3 different fields

    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!

  2. #2
    LillMcGill is offline Dagny fan
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2010
    Location
    Southern USA
    Posts
    70
    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.

  3. #3
    wes is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    29
    Thanks! I was misunderstanding PKs, figuring that as long as they only existed once in a table that was enough; I wasn't thinking about the fact that once combined with other data they would no longer be unique. Thanks for the explanation!

    I'm still confused on one point though. Say I make a table from the PKs from Personnel and Projects, and name the new table tblPeopleProjects. In this new table wouldn't Personnel be one field, and Projects be another field because I brought them in from 2 other tables? If so, how could the "combination of these serve as PK in the new table"? How do I combine them so that together they serve as the PK? Would this be in tblPeopleProjects, or would I make yet another table?

    Thanks for your patience!

  4. #4
    wes is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    29
    I just realized one more wrinkle -- some of the Personnel fields may be null; we may plan a project but not assign a person at the beginning. Will this affect using some combination of Project and Person as a primary key? We won't have a Person without a Project, but we could have Project without a Person.

  5. #5
    wes is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    29
    Ok, I thought I got the concept, and have been trying to play with junction tables so I can combine Person with ProjectName, thereby generating a unique record in which I can then identify a person's role on each project. But I'm getting hung up on what tables to set up and which things to link together via relationships.

    I have attached a new sample file where the basic table and funding queries work, set up for Access 2003. Could anyone tweak the actual file to include the Person plus Project Name table with the right relationships in place? Or is there another way to go about this?

    I'm simply not getting the steps, and I think once I see it I'll understand better, but I've been driving myself crazy playing with the different combinations of PKs and relationships and trying to figure it out with trial and error. Lots of error.

    Thanks so much!

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do not use table level lookups.
    see http://www.mvps.org/access/lookupfields.htm

    For a general approach to db design
    see http://www.databaseanswers.org/approach2db_design.htm

    and http://www.allenbrowne.com/casu-22.html

    For clarification of Junction Table
    see http://en.wikipedia.org/wiki/Junction_table

  7. #7
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    This is the structure I came up with. It probably will change.....

    At this point, I see at least 6 forms to add/edit data - 1 for each table. I would bind the forms to queries, not tables, even if the record source for a form is based on one table. Queries can be sorted, tables cannot be sorted.

    I split the name into last and first, got rid of the lookup fields, used autonumber as the PK for all tables and turned off autocorrect (Tools/Options).

  8. #8
    wes is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    29
    You've done it again Steve. I can now pull a report that shows the projects that each person is working on, based on the Query2 you set up. That has been one of my biggest roadblocks to date. Awesome!

    But of course, I have more questions.

    I added a new test record (ProjectF) to Query2 and it did indeed show up in tblProjects. I see that I now have a drop-down choice for Project F in the form ProPosPer you so nicely set up for me, where all the people are listed with their positions within a project. And when I added a new name to tblPersonnel it showed up as a new drop-down in formProPosPer.

    So if I'm looking at this right, when we add a new project that has 3 team members, we need to select it in formProPosPer three times, and on each line select one team member and his position.

    The problem is, we will end up with hundreds of projects, and dozens of personnel, which will make for a lot of scrolling and choosing, and potential mis-clicks. How can we just enter or view the project name in a single place, and select all of the associated people at the same time?

    Sorry, I'm not sure if I'm asking that question properly, I hope it kind of makes sense...thanks!

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I looked at the sample database. Good start.

    A few questions for your "business rules". May not be important, but if they are, you should design for it now.

    . a person could be on a project from Date A to Date B (dates needed)
    . could the lead change during the project - if so may need dates
    . can people be on multiple projects in different positions

    . do you deal with $funds requested and $funds received (this happens)
    . could projects be condensed / merged (or split during its life), if so could the funding be changed at that time

    Lookup fields at table level were there???

    Keep going at your design with possible query/questions/reports and adjust as needed. Make some test cases and make sure when you run them you get the answers you expected. Note any unexpected and make sure you can account for them (bad data, alter tables/relationships)

  10. #10
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    So if I'm looking at this right, when we add a new project that has 3 team members, we need to select it in formProPosPer three times, and on each line select one team member and his position.

    Sorry, I'm not sure if I'm asking that question properly, I hope it kind of makes sense...thanks!
    Yes, you are right and yes, it makes sense.


    The problem is, we will end up with hundreds of projects, and dozens of personnel, which will make for a lot of scrolling and choosing, and potential mis-clicks. How can we just enter or view the project name in a single place, and select all of the associated people at the same time?
    There are several ways this can be accomplished. Probably the easiest is to use a form/subform. In the detail section of the form is where the project name will be selected. It will be linked to a subform in the footer section, where you will select the person and the position.

    Of course, the project name will have to be unique or have another field to help identify the project. If you could have two projects with the name "Project A", you would need a way know what street/city/state/location/job number they were so the right funding/personnel/data is entered for the correct project.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    Thumbs up

    Quote Originally Posted by orange View Post
    I looked at the sample database. Good start.

    A few questions for your "business rules". May not be important, but if they are, you should design for it now.

    . a person could be on a project from Date A to Date B (dates needed)
    . could the lead change during the project - if so may need dates
    . can people be on multiple projects in different positions

    . do you deal with $funds requested and $funds received (this happens)
    . could projects be condensed / merged (or split during its life), if so could the funding be changed at that time
    Wes, This is why I said the structure would probably change. I figured there were a lot more fields (data) you didn't include. From what I know, projects are very date sensitive, you will probably have many structure changes.

    <snip>

    Lookup fields at table level were there???

    <snip>
    Orange, Yep, but I took them out in my example. I also turned off auto correct.

  12. #12
    wes is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    29
    Thanks for the great inputs, guys! I've been struggling with this for a while, I should have posted ages ago!

    @orange, regarding your business rules questions:
    Quote Originally Posted by orange View Post
    . a person could be on a project from Date A to Date B (dates needed)
    . could the lead change during the project - if so may need dates
    . can people be on multiple projects in different positions
    . do you deal with $funds requested and $funds received (this happens)
    . could projects be condensed / merged (or split during its life), if so could the funding be changed at that time
    Yes, the people on a project can change. However, while my data-geek tendencies would want to include dates to try to capture all the changes that may happen, I think that will be asking too much of the people who will actually be inputting the data. Our biggest need is to have a snapshot of who is on a project at the moment.

    Yes, people can be on different projects in different positions, which was one of my first roadblocks, as I need to be able to report by project and see everyone assigned to each one, as well as by people and list each project and the person's role within that project. ssanfu's sample structure got me through this one.

    We do deal with funds requested and received, but in our case we need to track funds obligated, which is based on my estimate, and the actual amount paid, which is based on the final bill. The tables and relationships I set up in my real database captured this data ok.

    Projects can be changed along the way, but only a fraction of our projects will actually need funding, and I will have sole control of the funding inputs, so I'm ok with updating those items manually.

    @ssanfu, regarding structure, you're right, this was just a sample test database, I have a many more items in my real one. I'll post separately a description of my real structure to see if you think my logic makes sense.

    Regarding how people work with the data and forms, I'd rather not have the users have to use a drop-down for the project name when they enter it. We won't have any two projects with the exact same name.

    However I would like for the users to be able to select team member names and position types from a drop-down list. I understand now why I shouldn't use lookups in a table; I take it I can set up a drop-down in a form? Also what would be the detriment of going back to a list where the last and first are in the same field?

    Thanks again guys, you've been great!

  13. #13
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    <snip>

    Regarding how people work with the data and forms, I'd rather not have the users have to use a drop-down for the project name when they enter it. We won't have any two projects with the exact same name.

    <snip>
    So a form/subform is the way to go.



    <snip>
    However I would like for the users to be able to select team member names and position types from a drop-down list. I understand now why I shouldn't use lookups in a table; I take it I can set up a drop-down in a form?
    <snip>
    Yes, you would use a combo box (aka " drop-down") in a form. Even if you had "look up field" defined at the table level, you would *still* have to create the combo boxes in a form!


    <snip>
    Also what would be the detriment of going back to a list where the last and first are in the same field?
    Part of the normalization process is to have all data "atomic" (no multiple values in one field).
    You can put the first and last names in one field, but if you ever want to use just the first name, if can get difficult to separate it from the last name.
    If you have the first and last names in separate fields, you can concatenate them either way: first & last or last & first. For selecting the personnel and position for a project, in the combo box for the row source you could use:

    Code:
    SELECT tblPersonnel.PersonnelID, [PersonnelLName] & ", " &  [PersonnelFName] AS FullName FROM tblPersonnel;
    "PersonnelID" (the PK) would still be stored in the table, but you would see "Smith, John" instead of two fields "Smith" and "John". If you feel comfortable with first and last names in one field, its your choice.

  14. #14
    wes is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    29
    Ok, I can see I need to get smart about forms/subforms and combo boxes! I've got some playing around to do with my sample database, I'm going to add in the fields I actually need in my real-life database and see how it goes. I'm betting I'll be posting again soon!

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    wes

    Yes, the people on a project can change. However, while my data-geek tendencies would want to include dates to try to capture all the changes that may happen, I think that will be asking too much of the people who will actually be inputting the data. Our biggest need is to have a snapshot of who is on a project at the moment.
    But the moment will change, and in fact could be 3 months from now,or
    Oct 15, etc..

    Many will argue that database design is not based on
    that will be asking too much of the people who will actually be inputting the data
    but

    Does the system meet our business needs?
    My earlier questions/points were meant to elicit business needs.

    How difficult/cumbersome a system is to use is an issue of design.
    You may want to think about that if you are the designer.

    Anyway, I'm sure things will evolve as your project proceeds.
    Good luck.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Personnel Roster with 4 sections
    By tat2z_21 in forum Access
    Replies: 8
    Last Post: 01-20-2011, 04:56 AM
  2. Replies: 3
    Last Post: 01-08-2011, 05:40 PM
  3. Replies: 3
    Last Post: 12-10-2009, 02:16 PM
  4. Using Access Projects
    By ragsgold in forum Access
    Replies: 4
    Last Post: 10-24-2009, 04:23 AM
  5. report to compare projects' targets
    By Arest in forum Reports
    Replies: 0
    Last Post: 07-29-2009, 09:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums