So I have two tables of Patients and Doctors and they are related
through a DoctorID number that is selected in a field on the patient's
record. This works good and well, theres a portal of all the patients
of any give doctor on the doctor's records, and there hasn't been any
problems.
However, if the patient changes doctors, we run into some problems
because it erases any trace that they were once with a previous
doctor. any suggestions on how to allow the Patient to switch Doctors
(an unspecified number of times) while still keeping a record of all
previous docs?
thanks in advanced. (:

jackie wrote:
new table <treatment> will help!

-jens

In article <1180549629.710050.313940 (AT) q69g2000hsb (DOT) googlegroups.com>,
jackie <justjacks (AT) gmail (DOT) com> wrote:

You need to make a many-to-many relationship, so any doctor can have
many patients and any patient can have many doctors.

Do this with an intermediate "join" table that has a field for PatientID
and a field for Doctor ID. The join table is related to the Patient and
Doctor tables by those two fields.

The join table would also have fields for any info peculiar to the
interaction of that patient with that doctor.

Each record of the join table could record one visit of that patient to
that doctor. So a patient with multiple visits to the same doctor would
have multiple related records in the Join table.

You maight call the join table "Treatment" or "Visit."

With this setup, you can keep complete history of each patient with each
doctor.

--
For email, change <fake> to <earthlink>
Bill Collins

Jens Teich wrote:
+-----------+ +------------+ +---------+