Join Table

[Julia]

Hi;

Thanks for looking at my question.

I am doing a database for a department in a hospital that
tracks the activity of doctors. The goal is to run a
report to see how many medical procedures each Dr. has
performed, or more importantly, the total time they worked
on patients. They are paid based on the time they
actually worked on patients, so this is like a timecard.

I get a table of scheduling information that is downloaded
from another source. This is the "master schedule" with
the patient names, procedures and time and Dr. scheduled.
This is routinely changed to reflect the actual schedule.

When a patient comes in for treatment, they can have 1 or
more procedures performed by multiple doctors. So for
example, you can have 1 procedure in the morning with 2
doctors working on you during the same procedure. Both
Drs need "credit" for working on you.

How should I set this up? I have an employee table with
the Doctor's info, the Master Schedule which is downloaded
but changeable. Do I need a join table that tracks
something? Or should I have an "Attending Dr." table
where each doctor on each procedure has a record?

Thanks for the help!

 

[Mike Sherrill]

How should I set this up? I have an employee table with
the Doctor's info,

See John Vinson's reply.

And, FWIW, do you *know* the doctors at that hospital are employees?
That's pretty rare where I live--most of them are independent
contractors.