I am creating an access database to create weekly reports on the activities of employees in our company. I have most of my tables set, but have run into a complication with one and am hoping to get some advice.
My tables are as follows:
tbl_Employees (fields are: empID, empFirst, empLast)
tbl_TaskOrders (fields are: toID, toNo, toTitle)
tbl_SubTaskOrders (fields are: stoID, stoNo, stoTitle, toID)
The way this works is that there are task orders (categories that activities and people fit into for purposes of invoicing), and within the task orders are subtask orders. For each subtask order there are either individuals or groups of people that work on it.
What I originally did was include a field in the Subtaskorder table called "stoMembers" and this was a multiple item, look up field that would contain the employee ID numbers of all those on the subtask orders. However, that is not working and I suspect it is poor design.
Does anyone have any advice for how this should be designed?