Results 1 to 3 of 3
  1. #1
    cassidym is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    44

    Multiple Fields Same Data

    Got an Employee database that has fields for Name, Office Number, Phone Number, Project, Date Arrived, Date Departed and Salary Level. Problem is my user now has discovered some employees are assigned to more than one project. And she wants to be able to show all the projects an employee can be assigned to.

    My approach is to create a seperate table with two fields; one is a look up list of all the employee names and the other a look up list of all projects. She would use this to assign project(s) to all the employees. I'll then design reports that show employees sorted by projects and one that lists each employee and their assigned projects.



    Or would it be more efficient to design just a projects table and somehow link it to the main table, for example via a query? Then maybe place the projects field in the main form as a sub form.

    Would appriciate any advice.

    Thanks

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    assuming each employee can be part of multiple projects and each project can have mlutiple employees, you will need 3 tables.
    1. tblEmployee - fields everything you have except for Project. also, add an autonumber field and set it to primary key.
    2. tblProject - ProjectID (autonumber - PK), ProjectDescription, whateverElse
    3. tblEmpProject - EmpProjID (autonumber - PK), EmpID (number - FK), ProjectID (number - FK)

    now you will open up your relationship window and put all 3 tables there. Click from the primary key (PK) of the Employee table and the Project table and drag to the matching foreign key (FK) on the EmpProject table. when the dialog box opens click "enforce referential integrity" then click ok for each.

    your layout should look like
    tblEmployee 1---M tblEmpProject M---1 tblProject
    the M will look like an infinity sign in the window though but it stands for one-to-many.

    now you will fill your employee table with one record for each employee. you will fill your project table with one record per project. you will then fll your EmpProject table with a record for each combination. if employee A is part of 3 projects, and employee B was part of 2, you put
    EmpProjID, EmpID, ProjID
    1, A, 123
    2, A, 124
    3, A. 125
    4, B, 124
    5, B, 125
    etc.

    you will then query off of tblEmpProject.

  3. #3
    cassidym is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    44
    Thanks TheShabz! I'll give it a try and let you know how it works

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 06-28-2010, 11:04 PM
  2. Constract of Multiple Fields
    By KLynch0803 in forum Programming
    Replies: 6
    Last Post: 02-01-2010, 07:27 AM
  3. Replies: 1
    Last Post: 12-10-2009, 08:41 PM
  4. Populating multiple fields
    By jjcaprio in forum Programming
    Replies: 11
    Last Post: 08-16-2009, 01:51 PM
  5. Multiple Fields In One Combo Box.
    By caljohn527 in forum Forms
    Replies: 1
    Last Post: 02-20-2009, 03:07 PM

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