Lespaul
Its very hard to get a full picture of how someone setup their Access app and database from a post. But I find each persons reply suggestions and comments interesting, enligtening, and containing new insights and ways to look at Access app and database design, development, maintenance,
and enhancement.
OK my project . . . I'll try to explain what I have done so far. On my company's web page under Access Database Developemnt there is a picture of the application's main form. If the Tek-Tip's moderator is willing I will provide the web page, to that page of our web page, URL. Or if you EMail me I will pass it along that way. I'm not trying to promote my company's services. Just provide a better "picture" of how I have setup my Access App from a form standpoint.
What I have setup does work and is being used in a medical cardiology practice. Now its time to add much more functionality to it and bring it in to regulatory compliance for medical software. At which point my involvement is done . . . I hope. Nope, I don't plan on marketing it. I will retain the intelectual property and Copyrights to it though. Just get 'er done (right) and then move on accepting the accolades from the practice staff but receiving NO financial renumeration for having created it. Yes, I am quite crazy!
Application Wise:
The app is organized in to a main form with tabs on it. Each tab contains patient data. The first tab contains a list box and text boxes from which a patient can be selected or searched for. There are command buttons that allow adding a new patient or editing an existing patient's demographic data. There is a tab where the user can select a report for the currently selected patient organized for each clinic. The other tabs on the main form contain the various clinics. Each clinic's tab contains a list box that displays the clinic's data from past clinic visits. There are also command buttons that enable the user to add a new clinic visit and edit (with administrative priveldge) a selected past clinic visit's data. Of course then an audit table has to be maintained to record any changes made to a patient's clinic's data and why it was made. The latter is a legal requirement mandated by HIPAA regulations and the great need and dessire to avoid law suits.
Table wise:
1) There is a main table called Tbl_PT_Demographics.
It contains patient demographic information along with
the ppractice physician that follows the practice, the
refering physician, clinics the patient is seen ion, etc.
It has a table index which is the table's primary key.
The primary key is called PT_DB_Number. It has a one
to many relationship to the patient's data records in
each Clinic's visits table. Each clinic table contains
the clinic visit data for all patients seen in that
clinic.
2) A patient may typically be seen in only one of the
clinics. But can be seen in more than one clinic
depending on their medical condition.
The clinics are CHF - Congestive Heart Failure,
Coumadin - Blood thinner monitoring
Diabetes - Control and monitoring
LIPID - Chloresterol monitoring
Hypertension - Blood pressure monitoring
Pacemaker/ICD - Heart rate regulator
devices
3) Each clinic visit table's fields are unique for the type
of clinic a patient is seen in. The only common field
items in all of them are:
name_Clinic_Tbl_Index Primary clinic table key
For example:
Coumadin_Clinic_Tbl_Index
PT_DB_Number Foreign key (the many part of
the one to many table
relationship)
name_Clinic_Visit_Date The date the patient was seen in
that clinic. For example:
Coumadin_Clinic_Visit_Date
Each type of clinic contains unique data fields that are
pertinent to the clinical data that would normally be
collected manually by the clinician.
For example in the Coumadin Clinc, Tbl_Coumadin_Clinic,
the data collected is:
Vital signs - Height, Weight, Pulse Rate, Resperation
Rate, Blood Pressure left and right arm, Lab name, Lab
date, PT a measure of blood thinness i.e. clotting rate,
INR - another more commonly used blood thinness, i.e.
clotting rate, Primary Diagnosis, Target INR value
range, Diagnosis 2, Diagnosis 3, Medication Name,
Symptoms, Clinic Visit Notes, Medication dosage
schedule, Next lab date, Next clinic visit date, and
clinicians initials.
While in the Diabetes Clinc, Tbl_Diabetes_Clinic, the
data collected is:
Vital signs - Height, Weight, Pulse Rate, Resperation
Rate, Blood Pressure left and right arm, Lab name,
Yes, the same vital signs but collected at the time of
the clinic visit. Therefore, if a patient is seen in
multiple clinics each clinic visit would normally be on
differnt days and times, Lab date, Blood suger level,
Primary Diagnosis, Target blood sugar level, Medication
Name, Symptoms, Clinic Visit Notes, Medication dosage
schedule, Next lab date, Next clinic visit date, and
clinicians initials, Clinican's full name. The latter
two entries make creating reports more straight forward
and may be required by regulatory laws, etc. as well.
One could argue that there should be a vital signs table
with a field that contains the clnic that the vital
signs data was collected in. Ah, but in each clinic's
tab on the main form there is a list box that displays
the past clinic's visits data ordered by decending date,
i.e. most recent first. If the clinic visit data is
actually in two tables it is a pain and cumbersome to
get it all together displayed in a list box or in to a
subform for editing.
Of course there are other tables containing the practice
staff, diagnosies by clinic type, medications by clinic
type, and other data that is pertinent to each clinic
type, etc. With pop up forms for entering and editing
this type of data comntained in them.
4) Deleting data from table entries.
The only data that can NEVER be totally deleted is that
belonging to a patient. However, to deal with a large
number of patients in teh database some patient's data
can be archived. Patients that have left the state or
are deceased would be candidates to have their data
archived from the database. Thier data would be moved
to a second database and some sort of list available to
know what patients have been archived. While it is not
very likely that a deceased patient will return to live,
a patient that moved could return to being seen by a
practice again. So one would like to be able to
"un-archive" that patient's data.
Practice staff entries can and should be deleted. By
storing their initials and/or full name in each clinic
visit's record there is a history of what clinician
saw the patient in that clinic visit. Maintaining a
seperate table for staff that have left the practice
really isn't necessary nor needed.
OK, here my implementation of the tables doesn't quit
follow data normalization. However, my contention that
there are situations where one may want to and/or need to
store actual data in a table rather than an index for
the data that actually resides in another table.
Well, long explanation of what I have done and why. Since this is a forum for learning I thought others might find this entry, in my post, interesting. May be not.
Best Regards,
