Unfortunately, you have picked a tough starting point for a possible database app. Not impossible, mind you - just difficult.
OK, let me put on the professor hat for just a moment.
The FIRST thing you should do is try to do by hand what you want to do by machine, for one or two examples. If you cannot do it by hand, you will never be able to teach your machine how to do it. Think of this as a tool for problem visualization and formalization. You need BOTH. You must visualize (be able to see) the problem and define rules to follow (formalize) when entering new data or updating data. Think of this as research before implementing a new procedure.
The NEXT thing is to look at factors you want to track and whether those factors are permanent or temporary. Like, having a cough today is likely to be temporary, whereas amputation tends to be more permanent.
OK, now consider classes/types of information. Each one is a candidate for table. When joining tables together, though, you often find a many-to-many relationship, which Access doesn't directly support. (It makes you take the long way around.)
I'm now speculating, the professor hat is half-way off and the spectator hat is half-way on.
You have patients. They have allergies. They make many visits. Each visit has the potential for discovering different symptoms. The diagnosis for each visit has the chance for changing from prior diagnoses and for being multi-faceted. I.e. Rhinitis and Bronchitis. You might prescribe different drugs for each situation. To me this suggests the following:
tblPatient
fldPatientID - perhaps just a number, even an autonumber, that is your patient's permanent ID - maybe even their account billing number. Your call. But this will be the table's primary key.
fldPatFName, fldPatLName, fldPatMName, etc. - personal, non-keyed data about the patient. fldPatDOB, fldPatMarried, fldPatSex (Despite the chance for a grin, don't make this one yes/no).
tblVisit
fldVisitID - another number, maybe autonumber, prime key of this table.
fldPatientID - foreign key that links back to the patient.
fldVisitDate, fldIsFollowUp, fldVisDuration, etc.
tblSymptoms
fldSymptomID - maybe an autonumber or maybe there is an official medical reference of symptom IDs. I know in the USA there is a diagnosis code, so I could imagine a standardized symptom code, too.
fldSymptomName
fldSymptomDetails
tblSympVisit
fldSymptomID - foreign key
fldVisitID - foreign key
fldSymptomSeverity - arbitrary code
fldSymptomSpecifics - maybe if you took the person's temperature, you would record the raw number here. Or blood pressure. Heart rate. etc.
You would have one entry in tblSympVisit for every symptom exhibited at that visit. You don't need the patient ID here because the VisitID links back to the patient. So that links the patient to the symptoms for a specific date and time. (Date is one of the Visit fields.)
OK, diagnosis, anyone?
tblDiagnosisCodes
fldDiagCode - a code number for the given diagnosis. primary key.
fldDiagName - a longer name for the associated disease or condition.
tblDiagVisit
fldDiagCode - foreign key from diagnosis table.
fldVisitID - the visit for which this diagnosis was applied.
Again, VisitID leads to the patient. The date from the Visit tables establishes when the diagnosis was made.
How about prescriptions?
tblScrips
fldVisitID - foreign key, leads back to patient
fldMedName - the name of the product
fldMedGeneric - yes/no, allowed generic fill
fldMedRefills - number of refills allowed, could be zero to indicate NO REFILLS
fldMedDose - the recommended dosage
fldMedFreq - the recommended frequency
fldMedSample - gave samples? yes/no
I think this gives you an idea of a possible structure concept. But I warned you it wasn't trivial.
I think the key to WHATEVER you choose is to separate out the patient from the visit. Use the visit as the key to everything else. Exploit the Visit table's PatientID to find the patient data. Use the Visit table to hold info that is date-sensitive.
But ... you could easily have tables that directly link back to the patient anyway. For one possible example,...
tblAllergies
fldPatientID - foreign key
fldAllergen - name of allergen: pollen, dander, latex, peanuts, particular medication name, mother-in-law, etc.
fldReaction - what happens when the patient is exposed: rash, cough, toxic shock, convulsions, edema, nausea, shivering, etc.
fldSeverity - i.e. not-quite-nil, mild, moderate, severe, fatal.
fldDelayed - yes/no if this patient's reaction is immediate or not. Or maybe a number of hours or minutes delay, zero meaning immediate.
Here, the basic principle is that some things depend on the visit; some depend only on the patient. Another way of looking at it is whether the data comes from a patient history form filled out before the initial visit or whether it comes from your notes of an individual visit.
