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