I have records containing video clips for various patients. Each patient's name can appear in several records in the table (Clips), e.g.:
Lastname1, Firstname1, Clip 1
Lastname1, Firstname1, Clip 2
Lastname2, Firstname2, Clip 1
Lastname2, Firstname2, Clip 2
Lastname2, Firstname2, Clip 3
I want to create a separate table (Patients) that consists of the last-first names, with a relationship of one last-first name in the table Patients to many clips in the table Clips.
What is the best way to create the Patients table? I can imagine a script that goes through each record in Clips, checks whether the Last-First name exists in Patients, and creates a record in patients if it does not yet exist. Is there a more direct method?
I would like users to be able to see a record for a given patient, view or enter clinical information for that patient, and see a list of video clips they can select for viewing.