none
Table structure for Access db with a users table RRS feed

  • Question

  • Hello everyone. I don't know why I have so much trouble outlining my databases sometimes, but I would like some advice. In the Access database I am putting together, I have a table named Projects, and a table named Employees. In the final database, I want to have a form that will allow the database user to see the details of a project, and in a subform see the list of end Employees that are allowed access to the project output.  What I am having trouble with is determining how to link the two tables together on the backend.

    I think I am doing this wrong because when I begin to populate the database with values it doesn't appear the way I envision it.

    Any ideas?  Thank you :)

    Here's what I have so far:

     

    Robert

    Tuesday, August 16, 2016 12:22 PM

Answers

  • Most likely, one Project will be accessible by more than one Employee, and one Employee may have access to more than one Project. This means that you have a n:m-relationship between the Projects and Personnel tables. Thus you need a third table tblProjectsPersonnel with fields AIRNumber and EmployeeID to resolve this. The primary key of this table consists of both (AIRNumber, EmployeeID), and you have 1:n-relations from tblProjects.AIRNumber to tblProjectsPersonnel.AIRNumber and tblPersonnel.EmployeeID to tblProjectsPersonnel.EmployeeID.

    Matthias Kläy, Kläy Computing AG

      
    • Marked as answer by greenthnkr Tuesday, August 16, 2016 1:20 PM
    Tuesday, August 16, 2016 1:02 PM

All replies

  • Most likely, one Project will be accessible by more than one Employee, and one Employee may have access to more than one Project. This means that you have a n:m-relationship between the Projects and Personnel tables. Thus you need a third table tblProjectsPersonnel with fields AIRNumber and EmployeeID to resolve this. The primary key of this table consists of both (AIRNumber, EmployeeID), and you have 1:n-relations from tblProjects.AIRNumber to tblProjectsPersonnel.AIRNumber and tblPersonnel.EmployeeID to tblProjectsPersonnel.EmployeeID.

    Matthias Kläy, Kläy Computing AG

      
    • Marked as answer by greenthnkr Tuesday, August 16, 2016 1:20 PM
    Tuesday, August 16, 2016 1:02 PM
  • Thank you Matthias :)

    Robert

    Tuesday, August 16, 2016 1:20 PM