Creating records in related tables
I have a database used for physical therapy documentation. There are three tables (actually several more, but these are the relevant ones for this problem): "Client Main," "Daily Visit," and "Weekly Visit Summary." Each of the Visit tables are linked to the Client Main via a Client ID# relationship.
Each day, a new record is created in the Daily Visit table for each scheduled client. For each client there may be one or as many as five visits in the week. For each client that is seen during the week, a single record is created in the Weekly Visit Summary. The Weekly Visit Summary table has two date fields: Week Start Date (always Monday)and Week End Date (always Friday) . Currently, I manually create this record at the end of the week by looking at a listing of clients seen that week.
I would like to automate the process if possible, so that when a record is created in the Daily Visit table, a record on the Weekly Visit Summary table would be automatically created and the Week Start Date and Week End Date fields auto-populated. In the Weekly Visit Summary, there can only be one record per client per week, so when a second record is created in the Daily Visit table in the same week, there would already be a record in the Weekly Visit Summary table for the week for that client. I think that I could probably come up with a script to be run at the end of the week to create the records in the Weekly Visit Summary table, but my preference would be have the records created automatically as daily visits are created, as it would streamline my documentation process.
Is there a way to create these records automatically, and populate the date fields in the Weekly Visit Summary?