Help with table setup

[primagic]

At the moment I have a table with a list of actions/tasks about 35 of them.

In another table I have a list of cases. The actions/tasks in the first table need to be common for every case.

The tasks are like a to do list for each case. Once the task is complete its marked as completed against the case.

I need the actions table to be simply a list. As the user will add to this list at times which will then have to be available for every case.

Can anyone recommend how I would go about setting up the database to record what tasks have been completed for which cases?

I was thinking just a running log that captures the caseID and the appropriate actionID along with date completed and so on.


Thank you

 

[TamarGranor]

Yes, a third table that links cases and actions and includes whatever additional information you need, like date completed, is the way to go.

Tamar

 

[primagic]

Do you have any idea how I would get a list of the actions have been completed and not completed in one list.

So for Case 1 I can see that Action 1, 2, 3 have been completed, but 4 and 5 have not, whereas Case 2, I can see that only 4 & 5 have been completed.

My aim is to display a complete list for each case which shows completed and non completed items for each case.

 

[TamarGranor]

One way is to create all the action records for each case when you add the case and use the completed date field to see which are complete. That makes sense if you generally need all the actions for each one.

Tamar