Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2006
    Posts
    1

    Unanswered: help needed with table relationships

    Ok im extremely new to this whole Access thing so I have several questions. First, let me give you an idea of what I am attempting: I am a member of a committee and we are involved in multiple projects. Each person is either inactive (working on no projects) or active (working on 1 or more projects). I want to set up a table that lists the member names, contact info, and which project(s) they are currently working on. I set up the basic table (not sure if its set up correctly or not though) but since some people are on more than one project, how do i make it so i can enter multiple values for the "Current Project" field? Also, I would like to make another table that lists Project Names, members (the members that are working on each specific project), and project description. How do I set it up so that under members (on the project table), it will gather the info from Member table (the one with contact info and such) and automatically list which members are working on each project? Also, There is one Leader per project and the rest working on it are just normal members. Can anyone suggest a good way to display who is leader on each project?

    if this is confusing, just ask and i'll try to clarify for you

    thanks, TRC

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    You really need to do some reading. It's worth getting hold of a good book that covers relational database design as well as access. Consider reading up/searching on the keywords:
    - relational database
    - primary key
    - foreign key
    - many-to-many relationship
    - queries
    - join queries

    To answer your question, I would create three tables with the fields as follows (including identifying the primary key in each):

    Members
    - MemberID (Primary Key)
    - MemberName
    - MemberTelNo
    etc

    Projects
    - ProjectID (Primary Key)
    - ProjectName
    - ProjectDescription
    etc

    Assignment
    - ProjectID
    - MemberID
    (both fields make up the primary key, each individual field is also a foreign key)

    The point of the Assignment table is the data will look like this:
    ProjectID___MemberID
    A0001______12
    A0001______13
    A0001______20
    A0002______13
    A0002______25

    See how this gives you the basis to answer your questions. Using queries we can list all members in Project A0001. Or we can list all projects that Member 13 is involved in. And using join queries we can attach the detailed member data or project data as appropriate.

    Once you have created the three tables you need to tell access how them are related. Click on the relationship icon in the main database toolbar. Here you can add all three tables to the view and then drag/drop the related fields onto each other e.g. drag the ProjectID field in the Projects table onto the ProjectID field in the Assignment table. This will create a link. That's your database design complete (except for the leader bit). You can add some data then create different queries to view the data in all the ways you've asked for (note you will need to add member and proect data before you can add assignment data). You can also create forms to give a nice front end to your database.

    Regarding the project leader bit, there are several ways to do this all with pros and cons. I think I would opt for adding a yes/no field called ProjectLeader to the assignment table which allows you to tick who the project leader is. However, you will need to find a way to constrain this so that each project can only have a max of 1 leader (although the projects I work on seem to have several leaders ). I would do this using a bit of VBA code.

    hth
    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •