Multiple table interaction
Having a bit of trouble understanding what I did wrong here.
I have 6 tables created with a number of fields. The tables are designed to capture various elements of a patient examination. They are Demographics, SubjectiveHistory, GeneralObjectiveAssessment, NeuroAssessment, ROM, ProgressNotes and __PrintPhysicianReport.
I need to track several things repetitively. For example, I have multiple patients, so I have a PatientID field in the Demographics table which is a primary key.
Each patient may have more than one injury, or attend at another time, so I have an EpisodeID (primary key) in the SubjectiveHistory table (not in the Demographics table, as the demographics will either be the same, or change and I don't have to track the previous demographics.).
Patients will attend more than once for a particular episode, so in the ProgressNotes table, I have a VisitID field (primary key)-not in the other tables because I'll only assess someone once for an episode, but will treat them multiple times for it, so I really only need visits tracked within the context of each episode.
Finally, I might write reports to a physician once, or more, so I have a table for the printed reports and need to keep old reports, so I have a ReportID field (primary key) within the __Print_PhysicianReports table.
I created foreign keys for PatientID within all of the other tables, relating them back to the Demographics Table. Then I created another foreign key in each for EpisodeID and related it back to the SubjectiveHistory table.
I expected that when I created a new record in Demographics, that I would be able to enter the SubjectiveHistory and other tables, but data entry seems to be blocked. It reports that there is no record. If I create a new record in the SubjectiveHistory it creates an entirely new record, completely blank and retaining none of the info entered in the Demographics layout.
I am attaching a pic of the relationships. Any ideas where I went wrong here?