I'm a medical student trying to set up a patient database for my school's free clinic. I have set up some tables in Access, including a Patient List, Diagnosis List, Treatment List, Physician List, and a table I called Encounter. The Encounter table has a form that I intend to be used each time a patient is seen where we can include notes, diagnoses, treatments, etc. I would like these encounter records to be tied to the patient records in my Patient List. I think I already sort of have that with a form I called Patient File which shows a record from Patient List with relevant data in a table from Encounter.

Additionally, I would like to be able to do the following:
-Autofilling of the encounter form with patient data (id#, name) if one or the other is typed in
-Updating of the patient list record for a field called "next appointment" based on what is typed in the encounter form
-A running list of diagnoses in the patient list record that is updated every time a diagnosis is selected in the encounter form
-Similar to diagnoses, a history of treatments
-A query to output all patients with a Next Appointment date within a given range (or even better generate a report with name & phone number)

Can some brilliant expert point me in the right direction with these tasks? I'd be eternally grateful!