designing database of patients

[selodor]

This will probably sound like a really stupid question, but here goes...
I am trying to design a DB to track patients throughout their appointments with the doctor. I have designed tables in a one-to-many relationship, patient to appointments. What I want to know is how to get Access to recognise that the appointment data belonging to a single patient makes up a set of records, distinct from other patients, rather than treating each appointment as an entirely separate record. This would enable me to use queries to show all patients' progress through consecutive appointments, rather than each appointment seeming to be a unique event not linked to previous or later ones.
Can anyone help me and the patients out?

 

[theprez]

The key here as with Access itself is relationships. You want to build a one-to-many relationship between the patients table and the appointment table.

To get a subset of the appointment table you can always query the appoinment table for a particular patient. Using the example by Uncle Gizmo, if you query the appointments table for patient 34, it will give you all of Mr. Smith's records.

To build a relationship, go to Tools, Relationships off the database window.

Reading your post further, if you need to somehow link appointments, for a patient, you will need two tables (Incidents, Appoinments)

Incident would have an Incident ID (maybe an autokey or some integer you control, the patientsID and other data relevant to this incident). The appointment table would have the incident id, patient ID and appointment date along with other appointment details.


Patient table (PatientID, Name, etc...)
2 Jack Clark
5 Mary Johnson
34 Robert Smith
54 Ray Lewis


Incident table (PatientID, Incident#, IncidentDate, Reason)
2 1 9/13/2004 Standard Visit
5 1 8/2/2004 Standard Visit
5 2 8/6/2004 Broken Leg
34 1 9/11/2004 Check UP
34 2 9/13/2004 Military Medical Forms

Appointment Table (PatiendID, Incident#, AppointmentDate, Dr. etc...)
2 1 9/13/2004 08:45:02AM Killdare
34 1 8/7/2004 08:30:00AM Cagney
34 2 9/13/2004 12:08:00PM Green
34 2 9/14/2004 1:00:13PM Green
34 2 9/15/2004 12:30:23PM Green


Now, to get a set of linked appointments for Smith (PatientID 34), you would query by PatiendID and Incident ID. Hope I'm not way off on my thinking here.

 

[selodor]

Measuring change in all patients rather than individually

Thanks fellas -

I'm with you so far, but my problem comes when I want to check the progress made by all patients, rather than individually. Say we have 200 or so patients taking a certain drug, and I want to see how effective that drug is overall. What I would want to do is to check how the patients' scores on a measurement scale have changed over the course of their appointments, but because going through it patient by patient would take forever, I would like to find a way to get Access to analyse the data for me. Ideally, it would be something like subtracting each patient's score at their most recent appointment from their initial score, and then I could see how many results came out positive and how many negative, and therefore how many patients have improved with the drug and how many haven't.

The complication is that since new patients are starting the drug all the time, they will all have a different number of appointments under their belt. So while I can easily get all the starting scores by selecting the appointments with session no. 1, getting the most recent scores is trickier (it could be session no. 2, no. 3, no. 4, it varies from patient to patient). I've tried using the 'Maximum' aggregate function to get the highest session no. for each patient (Query using MaxofSession No., then Patient ID), which works OK, but then I can't get the measurement scale score that corresponds to that session (it just gives me all scores across all appointments and patients).

Anybody have any ideas?