New Database Set-up

[LMB]

Hello Everyone,

I am going to set up a new database and I need help with the tables. I also
need help with setting up everything else so I'll be asking questions the
whole time I set it up. I want to do it right from the start and this seems
like a simple enough database to start with.

The purpose is to keep track of total unnecessary breathing treatments
ordered by physicians. When we add up all the unnecessary treatments we
will then determine how many extra therapists were needed (FTE's Full Time
Equivalents) to do these. Right now we are keeping track with an excel
sheet.

Date-this is the date the audit is done and is done everyday that the
patient is on treatments so some patients have 1 date and others have many
dates.

Patient-Currently they are using Last Name only. I think we need to use
Medical Record Number in case we ever need to look something up and also we
should have L name and F name

Doctor-Currently they are only using Last Name but there are many physicians
who have the same last names so I suggested using F and L name and possibly
obtaining physician ID numbers.

Diagnosis- Respiratory Diagnosis

Treatments ordered for a 24 hour period-The number of treatments a patient
is getting based on physician order.

Treatments that would be given if the patient was on an Order set-Number of
treatments that would be given if the Respiratory Therapist had placed them
on an order set.

Difference between the what the doctor would have ordered and what the
therapist would have placed the patient on-This would be calculated.

Facility-There are three hospitals we are tracking this in

We are using the data to prove that order sets reduce unnecessary treatments
and show how many extra treatments are being done per 24 hour period and
Weekly and Monthly and Yearly

I am thinking this is how my table structure should look. I put a P after
the field I think should be the primary key.

tblAuditDate
numAuditID (autonumber) P
dttAuditDate

tblPts
strMRNumber P
strPtLastName
strPtFirstName

tblDoctor
PhysID P
PhysLastName
PhysFirstName

tblTreatments
numTreatmentsID (autonumber) P
numPhysOrderedTxs
numOrderSetTxs

Another sheet that I see in the excel workbook is the number of pts on order
sets vs patients on therapy. I guess we will need to make a table for this
as it is independent of the audits. All patients are not audited but all
patients are counted for this one. I think he uses this to show the
potential # of unnecessary treatments for all patients based on the above
findings.

I have read about junction tables and I think I need some but I really don't
know how they work or how to build a query or make a form using them so if
that can be explained using my database I may understand it.

Thanks,
Linda

 

[LMB]

Whoops, I just noticed I forgot the table for facility. There are only 3

tblFacility
numFacilityID (autonumber) P
FacilityName

 

[John Nurick]

Hi Linda,

The way things have to be done in Excel is seldom the right way to do
them in Access. Always, you need to start by working out what are the
real-world entities you need to model. Here, by the sound of it, you
have something like the following:

Patients (and yes, you'll need to store enough other information to
ensure that when you get two patients with the same first and last names
everyone can tell them apart).

Doctors (ditto)

Facilities (if there are few of these and they seldom change their names
it's simpler not to use an "artificial key" FacilityID : instead just
store the facility name in the other table.

TreatmentOrders: i.e. an order for treatments issued by a doctor for a
patient. You'd need to store sometihng like TOID, TODateTime (presumably
it's possible for a patient whose condition changes to have two
diagnoses in one day), PatientID, DoctorID, Facility,
TreatmentsOrderedByDoctor, and - I presume - information about what the
doctor thought was wrong with the patient that they needed the
treatments.

Audits: if each patient is audited every day, then there may be one or
more Audits per TreatmentOrder. So you'd need to store TOID (which links
to a TreatmentOrder and therefore to the PatientID and DoctorID),
AuditDate, TreatmentsRecommendedByTherapist, and - I presume,
TherapistID so you can also audit the respiratory therapists.

You may also need to consider whether and how to track patients across
multiple admissions or episodes. And if the database is for actual use
you must make sure it complies with the relevant data protection and
health service regulations.