subform with previous dates

[Vickie English via AccessMonster.com]

Hello,

I have a form called DailyActivities with field names including patID,
patName, visitDate, TotalON, etc. Patients may have several different
visitDates but each time they visit they have the same patID and some other
(different) information(all on the DailyActivities form).

I'm trying to pull all visits for a particular patient together most likely
on a separate form called previous Visits (I guess this form will have a
subform on it showing all the visits). My DailyActivities form will have a
command button to call the previous visits form

When I select a date on the subform I'd like the visitDate's information to
show.

I created the form with the subform but unfortunately it shows visits from
other patients. Could someone please give me some ideas. I know there's a
lot in here so please let me know if you need any further clarification.
Thanks.

VE

 

[Guest]

This problem may go to the underlying tables if they are not normalized
properly. It sounds to me like you are only using 1 underlying table which
needs to broken into two or more tables. One table should be devoted to the
patients and only contain fields that relate to the individual patients. The
second table should relate only to visits and contain a foreign key that will
link it to the Patient table. i.e.:

tblPatients
patID
patNameLast
patNameFirst
patDateofBirth
patSSN
....

tblVisits
visitID
patID
Date
Reason
Diagnosis
....

Once you have the tables separated like this and relationship between them
defined (Tools/Relationships...), then it is very easy to creat a form based
on a query that pulls information from both tables. By using the patID you
can access only information for the selected patient and show all their
visits in a subForm as you desire.