I need some direction on the best way to set-up a database I am working on.
Right now I have 2 tables titled 'Employee' and 'Tasks'
Under the 'Employee' table I have the following fields: employee name, department, location, and phone number.
Under 'Tasks' I have the following fields: department and tasks.
Example of Tasks Table
Department________________Task
Customer Service_______________Answer Calls
Customer Service_______________Document
Customer Service_______________Make Outgoing Calls
Accounting____________________Special Project
Accounting____________________Meeting
My end goal is to create a form that will pull up each employee and list the specific tasks available for that employees department and allow the user to select 'Yes' or 'No' on each task.
Each department has different tasks that they perform, and some departments have more tasks than others.
My thoughts are to create a main form and then have a button that the users click on and it will pull up a sub form that tasks can be selected.
I am not sure the best method to store the tasks in the table.
Once the tasks are selected on the form where and how should I store them in the table? (Would the best way be to create a field for each task and return the 'Yes' or 'No' value to each field? The only problem is that I could end up with 100+ tasks between all the departments.)