Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 30 Times in 30 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts