How do I record repeated information for multiple appointments?

[roshy]

I'm trying to build a database where we record the same information multiple visits for individuals.






Below is what I had in mind for how it would look, but I am having trouble getting each sub-form to correspond with each visit. What would be the best way to do this?



I've experimented a little with queries but am not sure if I'm barking up the right tree, and can't find any examples. Any help would be appreciated!

[orange]

Work through this tutorial on database design.

Good luck.

[Missinglinq]

We can't emphasize enough the importance of learning the basics before starting to develop an Access database, so please, please, please read through the orange gave you the link for!

Having said that, and so that you have something to relate to your own situation, while reading the tutorial, you basically need three Tables here; a Patients Table, a Visits Table and a Visit Details Table, with Fields similar to this:

PatientsTable
PatientID (Primary Key)
Patient LastName
Patient FirstName
Patient Address
DOB
......and so on

VisitsTable
VisitID (Primary Key)
PatientID (Foreign Key)
VisitDate
Visit Time
......and so on

VisitDetailTable
VisitDetailID (Primary Key)
VisitID (Foreign Key)
ClinicalDetails
Questionnaire
......and so on

You'd then have a Main Form based on the PatientsTable with its Subform (in a one-to-many relationship...Patient-to-Visits) based on the VisitsTable. The two would be related based on their PatientID Fields.

This first Subform would then have its own Subform (also in a one-to-many relationship.. Visits-to-VisitDetails) based on the VisitDetailTable. These two would be related based on their VisitID Fields.

As you move through Patients in the Main Form, the Visits and Visit Details will cascade, only showing Records related to the current patient.

That's the way the really purists among developer would do this, but if ClinicalDetails and Questionnaire are truly the only Fields you need as Visit Details, you could actually add those Fields to the VisitsTable and get away with a Main Form and a single Subform, with your Tables set up like this:

PatientsTable
PatientID (Primary Key)
Patient LastName
Patient FirstName
Patient Address
DOB
......and so on

VisitsTable
VisitID (Primary Key)
PatientID (Foreign Key)
VisitDate
Visit Time
ClinicalDetails
Questionnaire
......and so on

Linq ;0)>

[roshy]

Hi orange and Linq,

Thank you so much for the advice! It helped me a lot... but now I have a new conundrum.

I thought I had the subforms set up properly, and had linked them to a drop-down box to control which subform was visible.

However I have discovered that the subforms don't contain the information that I want them to! For example Visit 2 will show the information for Visit 2, and Visit 5 will also show the information for Visit 2! I am tearing my hair out over this. Ideally selecting Visit 1 will show Visit 1 data, Visit 2 will show Visit 2 data, etc...

I have attached a model of the database. Any ideas where I have gone wrong?

Patient Visit Database.zip