It's late, and I'm stumped approaching this issue.
I have one table "Student". It has a relationship with a table named "Term". "Term" is related to a table named "Term Dates". "Student" is also related to a table named "Scheduled Term"
Here's the logic behind the table setup. Terms are a set of 13 dates. Terms are predefined (think template). "Students" are assigned to the "Term". However, in order to track attendance or to manage a rescheduling, I want to copy the records in "Term Dates" to "Scheduled Term" when the "Student" is assigned their schedule. The user selects the term from a pop-up list, there is a button to "Assign" that term to the student in a layout displaying records from "Student".
The dates in "Term Dates" will not change and serve as a template to expedite assigning the dates to the students. "Scheduled Term" is where I want to manage attendance and rescheduling (change of dates or any date deviation on a per student basis).
All I need to do is copy the related records from the "Term Dates" table into the "Scheduled Term" table. The primary key from the "Student" table will be copied as a foreign key into the "Scheduled Term" table on each row that also contains the "Date" field from the "Term Date" table.
Tables/relationships attached in graphic.
It should be really easy, but I'm out of coffee and need to get some sleep before this drives me insane. Any help/guidance is greatly appreciated.