putting data from several tables on one form

[Guest]

I am trying to build a database for planning operations in a small surgical
theatre complex. We have three theatres and nine surgeons. My ultimate goal
is to produce a single form with a calender to choose a date which will bring
up which surgeon is operating that day in each of the three theatres and then
a subform so if you select the theatre it displays the patients on the list.

This seems straightforward but I am finding it difficult to get all three
threatres on the same form. If I make a table which holds data on all three
theatres it is impossible to match the theatre with the operation, and if I
build three tables, one for each theatre I cannot put all the information on
the same form.

How should I set up th edatabase to achieve this?

 

[John Vinson]

I am trying to build a database for planning operations in a small surgical
theatre complex. We have three theatres and nine surgeons. My ultimate goal
is to produce a single form with a calender to choose a date which will bring
up which surgeon is operating that day in each of the three theatres and then
a subform so if you select the theatre it displays the patients on the list.

This seems straightforward but I am finding it difficult to get all three
threatres on the same form. If I make a table which holds data on all three
theatres it is impossible to match the theatre with the operation, and if I
build three tables, one for each theatre I cannot put all the information on
the same form.

How should I set up th edatabase to achieve this?

STOP... you're apparently starting the project from the wrong end. You
should build properly normalized tables *first*, and *then* build your
form to fit the tables, not vice versa!

I'd suggest a table structure like:

Theaters
TheaterNo <Primary Key>
<any descriptive information about this OR>

Staff
PersonID <Primary Key, maybe a badge number or employeeID>
LastName
FirstName
Role <e.g. Surgeon, Anesthetist, Nurse, ...>

Patients
PatientID <your hospital patient number
LastName
FirstName
<other bio information>

Operations
OperationID <autonumber primary key>
TheaterNo
StartTime <date & time in one field>
CompletionTime <ditto, starts with estimate>
PatientID <who's being operated on>
<other information about this operation>

OperationStaff
OperationID <long integer>
Role <e.g. Surgeon, Assistant, Head nurse, ...>
PersonID <link to Staff>

Your Form should NOT have a box for Theater1, and a different box for
Theater2. Instead, you should treat that information AS DATA to be
displayed. You can easily filter the form using a Query to see only
one theater, or all three theaters for the timespan in question, using
a continuous form, or whatever layout is most effective. My main point
is that you must *NOT* design the tables to fit the form - but rather
the tables should be designed logically to fit the data, and then the
form should be set up to fit the tables.

John W. Vinson[MVP]