Collecting and reporting session data

I am building an app for therapists to collect session data on clients
I have a the following relevant tables:
-Goals assigned to Client
-Session Reports

I would like to make a form for Session Reports

All Tools are available for all Clients and with any session the therapists may use any number of the tools (i.e. of the 10 types of tools a therapist might only use 3 in one session and 5 the next). Displayed as an Enum?

As for Goals, specific goals are assigned to each client and the therapist might only work on some of the assigned goals and capture session specific information about that goal like number of tries and notes(i.e. of the 100 possible goals, only 20 are assigned to the client and in one session only 5 of those 20 are worked on. With those 5 the therapist records XX number of successes and enters notes for each of those five. Each day the therapist can work on any of the assigned goals. Over time the goals assigned can change)

Currently All I can think of is having to change my table layout (Columns) as goals are changed. I would like to avoid that.

I would like to run queries/reports/graphs against this data in the future.

I can’t help but think that this is a common need and that there is some common methodology name for it that I can google and educate myself.

Can anyone help point me in the right direction?

Thanks in advance

Hi Mark - I’ve built out a sample app which I believe meets your use case: In the app you are able to add new patients, assign them goals, and then track activities/reports against these goals. The relationships between these different data points are established via references

If you want the form to change in response to what activity you have selected, you can do so by including the columns for all activities in one table, and using Show If functions to hide/show form fields based on what activity has been selected. Here is a step by step guide to that process!


Thanks for the Reply

I Looked at the sample app you posted (thanks by the way).

After Watching some database Training videos I realized that my plan was not following proper data normalization procedures.

So I decided to create tables for Objective Utilization by sessions.
[Objective use ID],[Session ID],[Objective ID],[Notes]
Still working out some issues but here is the flow I am trying to accomplish.

I created a session form with virtual columns referencing the “Objective Assignments”

When “from a client record” I select to create a new session.
This new record form auto populates the client, and I enter the date and time.

Upon clicking save I set it to automatically go into the detail view of the session that I just created.

Now I see the details of the session I just created.
I added a virtual column that displays the Objectives assigned to this client
[Objective Assignment ID],[Client ID],[Objective ID]

Clicking “New” here would just add a new objective and not a use of the assignment

I am trying to figure out how I can just click the objective and NOT be taken into the details of the “objective assignment” but rather create a new record in the “Objective Utilization” table where the [Session ID] and [Objective ID(of the objective clicked)] are automatically passed into it. I can then enter my notes and click save.

Then be taken back to the Session Details and have my “Objective Utilization” entry displayed below that objective and then repeat for any other objectives.

Thanks again for the response.