Need Help for entire database

[LMB]

Hello everyone,

I started working on a database a couple of months ago and got help in the beginning designing the tables. I am working on it again and need to get it finished soon. I know I need help on every aspect. I have several access and database books but as a 44 year old, my learning style is a bit different and I need to do and see to learn. I do not have a computer or programming background but would like to improve that situation. I have made some other simple databases with help from this group on and off for the last couple of years. I am a Respiratory Therapist. What would be the best way to utilize the groups? Should I post all my questions in here or go to the specific groups (forms, tables, queries, reports) when I get to that point? Here is what I have so far...

Thanks,
Linda

The purpose of the database is to audit patient charts and compare the number of treatments actually ordered to the number of treatments actually needed based on our protocol criteria.

We want to report the physicians who are not using protocols, how many extra treatments are being done that aren't necessary based on our protocols and then make an equation to determine how much time and money would be saved if the physicians would use protocols monthly/quarterly/yearly.

There are 3 hospitals in our study.


TblFacility
FacilityID (pk)
FacilityName

TblPatient
PatientID (pk)
FacilityID (fk)
PatientLName
PatientFName
PatientAdmitNumber
PulmonaryDiagnosisID (fk)
AdmissionDiagnosisID (fk)

TblPulmonaryDiagnosis
PulmonaryDiagnosisID (pk)
PulmonaryDiagnosis

TblTreatments
TreatmentsID (pk)
PatientID (fk)
AuditDate
TreatmentsOrdered
AuditTreatments
PhysicianID (fk)
AuditorID (fk)

TblPhysician
PhysicianID (pk)
PhysicianLName
PhysicianFName
PhysicianNumber
PhysicianSpecialtyID (fk)

TblAdmissionDiagnosis
AdmissionDiagnosisID (pk)
AdmissionDiagnosis

TblAuditor
AuditorID (pk)
AuditorLName
AuditorFName
AuditorLogInName

TblPhysicianSpecialty
PhysicianSpecialtyID (pk)
PhysicianSpecialty

 

[Jeff Boyce]

Linda

If you don't get your data structure nailed down first, you'll be "paying
for it" in everything else you try to do. If you haven't already, check
into the topic of "normalization". For example, when I looked over the
table structure you posted, I found a "patient" table used to relate a
patient to a diagnosis. This table contained both patient-related data and
diagnosis-related data. Does this mean that you will only see a patient for
one diagnosis? Or that you don't care to keep any historical information
about previous diagnoses for any given patient?

A more-normalized design would have a table for patient info (name, rank,
serial number, and an "admitID" ONLY if the same patient is given the same
admitID EVERY time s/he comes in), and a table that related a patient to a
diagnosis (PatientID, DiagnosisID, DateOfDiagnosis).

I suspect treatments are related to a diagnosis (one diagnosis, one-to-many
treatments). I didn't spot where this is done -- again, a one-to-many
relationship really needs a one-to-many table row relationship.

As for your general question, my suggestion would be to focus on a specific
aspect (data, form, report) and ask a specific question. General questions
get general answers.