Results 1 to 2 of 2
Thread: History Table (2000)
2004-12-03, 23:07 #1
- Join Date
- Dec 2002
- Thanked 0 Times in 0 Posts
History Table (2000)
Hi, we have a list of our patients. Last name, first name, DOB, SS #, Date, DR
We have the Doctors name coming from a lookup box. The problem is they want to start keeping a patient history, telling them the Dr and the date for every visit. Right now if someone comes in we are just changing the Drs name from the last Dr they seen to the new Doctor they are seeing, by doing this we have no idea which Dr they seen on their last visit. What do we need to do to set something up that every time we find the patients name from the list and pick the Dr from the drop down list, this information will be kept? Can this be done?
Thanks for your help. Almost 3 weeks on the job working with access and with your help I
2004-12-03, 23:33 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: History Table (2000)
You will have to split your table in two:
<UL><LI>A Patients table with personal information: PatientID (primary key, for example an AutoNumber field), LastName, FirstName, DOB, SSN (plus further patient-related info if necessary)
<LI>A Visits table with information about a patient's visits: PatientID (to be linked with PatientID in the Patients table; NOT an AutoNumber field), VisitDate, DoctorID (plus further visit-related info if necessary)[/list]The tables should be linked (in the Relationships window) on PatientID, with Referential Integrity enforced. The tables have a one-to-many relationship: there can be many visits for one patient.
The best way to present the data is in a main form based on the Patients table, with a subform based on the Visits table, linked on PatientID. If you go to a patient's record in the main form, the patient's visits will be displayed in the subform automatically. If you create a new record (=visit) in the subform, the PatientID will be filled in automatically with the current PatientID from the main form.