When data is saved how can some fields be saved in another table.

[DPROMICS]

I have a table, Tbl_Coumadin_Clinic, it has a primary key of its own and a key that is linked to a main table, Tbl_PT_Demographics, in a one to many with the Tbl_Coumadin_Clinic table.

There is another table called Tbl_Clinic_Visit_Vital_Signs. It is also in a one to many relationship with the Tbl_PT_Demographics table like the Tbl_Coumadin_Clinic table.

In the Tbl_Coumadin_Clinic table there are fields for patient vital sign data. After data has been entered in to the clinic visit subform and the user clicks on the command button, "Save Clinic Visit" data is saved in the new record entered in to the Tbl_Coumadin_Clinic table. However, I need to be able to also save the the patient vital sign data entered in to the Tbl_Clinic_Visit_Vital_Signs table.

I assume that this would be performed in the function code of the command button. But what is the syntax to do this?

Best Regards,

 

[Crowley16]

create a insert query to insert values into these 2 tables, or even 2 seperate insert queries if you want...

and in the code, you can either directly run the sql with docmd.runsql or you can execute the query by executing the query object of a saved query.

--------------------
Procrastinate Now!

 

[fneily]

Are you saving the same vital signs information in two tables? Why? Unless you have a special reason (a legitimate one), this goes against the grain of a relational database. Do you really need to do this? Did you normalize your tables? I've never seen a database for a clinic that had to do that.

You can also use code to update fields in a table such as:
Private Sub YourCommandButton_AfterUpdate()
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim strWhere As String

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Tbl_Clinic_Visit_Vital_Signs", dbOpenDynaset)
strWhere = "[PrimaryKey] = " & Me![FieldOnForm]
RS.FindFirst strWhere
If RS.NoMatch Then
MsgBox "No match found"
Else
RS.Edit
RS![TableName] = Me![FieldName]
RS![TableName2] = Me![FieldName2]
Etc.
RS.Update
End If
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing
End Sub

Or use the RS.ADDNEW command for a new record

 

[DPROMICS]

Thanks for the response and suggestions.

I haven't rulled out a query to gather the patient's clinic visits vital signs data and display the vital sign data in a LIST box. Theoretically/technically, that is the better method.

However, my concern is that as the database grows with patients clinic visits the time required for the query to complete will start taking minutues rather than a few seconds. Users get impatient waiting for things to happen in a program.

Remember the query will have to search every clinic's

(
Coumadin - Tbl_Coumadin_Clinic,

Diabetes - Tbl_Diabetes_Clinic,

Hypertension - Tbl_Hypertension_Clinic,

LIPID - Tbl_Lipid_Clinic,

Pacemaker/ICD - Tbl_PacemakerICD_Clinic, etc.
)

visit tables searching for matches to the currently selected patient. The patient is selected from a list of patients in the Tbl_PT_Demographics. Which has a one to many relationship to the entries in the various clinic visit tables.

Now in the Tbl_PT_Demographics there are a set of fields that contain what clinics
(
[Tbl_PT_Demographics].[Clinic_1],
Clinic_2 . . .Clinic_5
)
the patient is currently seen in.

In a query can one look at these fields and based on their content only search the clinic tables that the patient is seen in? Seems to me that would involve being able to do IF Then Else which I don't know if that can be done in a query. May be one can. I am hardly an expert on querys although I have written a couple of simple ones.

OK if I do it with a query, I believe the syntax involves
multiple SELECT statements in the query. Yes?
What is the basic syntax?

Best Regards,

 

[PHV]

Why not having a look here ?

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DPROMICS]

Thanks for the reference. I will read it over.

I understand database theory and what is accepted best or at least good practice.

However, reality steps in. In my case, say a practice staff member leaves and their entry in the Tbl_Practice_Staff table is deleted when they leave the practice, which is reasonable, then there is a problem with storing the index in to the Tbl_Practice_Staff for that staff member in any patient clinic visit table entries because the index for that staff member is then no longer valid. Yes, that is considered best/good practice. But the reality, at least in a medical setting, is that it is not acceptable.

For HIPAA, legal, and patient record completness purposes it is necessary to actually store the practice staff member's initials and/or name, that saw the patient in the clinic visit, in each clinic visit record for each patient.

My quandry, trying to create an Access database and app that is easy to use and looks like a polished professional program that meets teh needs of the clinical user while making decisions as to where theory and good/best practice can and should be observed and where it has to be a secondary consideration to HIPAA, legal, and patient record completness requirements.

And do all of this to get a freebee project I got myself in to two years ago fully completed so I can go do much more billable time work again.

Again thanks to all for responding to my post. I have to now analyize how I think this needs to be done and then try it to see if it will work as needed.

Best Regards,

 

[lespaul]

In my case, say a practice staff member leaves and their entry in the Tbl_Practice_Staff table is deleted when they leave the practice, which is reasonable,then there is a problem with storing the index in to the Tbl_Practice_Staff for that staff member in any patient clinic visit table entries because the index for that staff member is then no longer valid. Yes, that is considered best/good practice. But the reality, at least in a medical setting, is that it is not acceptable.

I don't consider deleting ANY data from any table reasonable. Create a field 'Terminated' or 'Active' and change that when the person leaves. How are you ever going to be able to report who did what to a patient if you're deleting the associated record?

I don't consider anything that you stated in the above paragraph to be considered best/good practices and have actually never seen or developed a system that deletes any information unless it is a time sensitive issue (one of my programs deletes associated image files after three months but that is stipulated by law - I still keep the record of the individual).

Why would you develop separate tables for each "clinic"? Is the information you are storing in each of those tables the same? Visit Date, Patient ID, Caretaker? If so, then you have not developed your tables to the 3rd normal form. You should have a single table with a field to determine which clinic was visited.

A basic structure would be something like:
[tt]
Patient
PatientID
{all other information about the Patient}

Staff
StaffID
StaffName
{all other information about the Staff, including active?}

Clinics
ClinicID
ClinicName
{all other information that is specific to the different clinics - room number, phone number, etc.}

PatientClinic
PatientID
ClinicID
{allows many patient to be assigned to many clinics}

ClinicStaff
ClinicID
StaffID
{this will allow for each clinic to have many staff members and allow the staff members to work in many clinics}

ClinicVisits
VisitID (PK)
PatientID (FK)
ClinicID (FK)
StaffMember (FK)
VisitDate
{any other information about the visit}

Testing
TestID
TestName
{any other information specific to the test: cost, covered by insurance}

VisitTest
VisitID
TestID
Results
PatientNotified
[/tt]

That's a basic start from what I've gleaned from your posting, but it should point you in the right direction.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[DPROMICS]

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,

 

[lespaul]

Thank you for providing additional details. However, nothing you said changes the fact that your tables are not normalized. Your original post was a question on how to get the same data in two places in your tables:

data is saved in the new record entered in to the Tbl_Coumadin_Clinic table......However, I need to be able to also save the the patient vital sign data entered in to the Tbl_Clinic_Visit_Vital_Signs table.

Just by having the same data in two locations you are breaking normalization rules.

In your table descrption you mention several times how this structure works with the forms, data storage and presentation are two separate issues. You should not design your tables with the focus on how the forms will work. An application should be designed to make the forms work with the correct table structure.

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.

Isn't this what your current problem is? Having clinic visit information in two tables?

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.

I don't understand what you are trying to say here? you don't store information in an index.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[DPROMICS]

Lespaul -

Quote: I don't understand what you are trying to say here?
You don't store information in an index.

No, the concept is that you store an index in a record's data field of a table. The index "points" to where the data is actually stored in a record's field of another table. Look I fully understand database design theory and practice and have worked with that over the past many years. Just not in the "Access" paradigm.

Now Database theory dictates that this is the way it should be done. And in many aspects of what I am doing, my project does comply with database theroy. However, in some situations I made the design/command decision that for reasons out side of "database theory", that rather than an index being stored in a record's data field in a table, the actual data should be stored there rather than an index.
Yes, it violates Normalization. One may not agree with that choice. But, it was mine to make and it was made based on sound engineering design principals not soley on database design "dogma". "Dogma" for lack of a better perhaps more appropriate (?) word.

I did, based on the needs of the clinical users, define the data, the tables that will store the "data", and the relationship between them before starting the design and layout of any forms. However, while the database design is important the design of the forms that allow the user to interact with the data stored in the database is also just as important. Its not a horse before the cart situation. Its a team of horses working together cooperatively.

My primary overall design philosopy and parameter has been what makes sense and what doesn't. I've found that design philosophy to have served the products I have designed and developed over the past thirty years quite nicely.

I do appreciate your replys and I have taken your comments and suggestions in to consideration. In fact part of today's effort is to take a look at my design and the choices I have made in the project's implementation. So far this morning I haven't found anything I will change in the project's design to completely conform to database design "dogma", because it simply doesn't make sense to do it that way in this project.

My choice, my descretion, and ultimately my decision based on 30 plus years of experience with many types of software and hardware design, programming projects. I guess just like the bumble bee that seems to care little about aerodynamic theory that dictates it can't possibly fly, it gets the job done anyway, quite nicely, and more importantly, reliably.

Well, back to work. I can not afford to take any more time to discuss or debate the design choices i have made. I have a project to get completed by the end of this month.

Best Regards,

 

[PHV]

So, as you want to maintain dups in sync, I strongly suggest you to play with a REAL database engine having Triggers and Stored Procedures ...

 

[DPROMICS]

PHV and Whom ever else . . .

It was not what I wanted, it is what I got myself in to. And I am doing it as I have the time available, from doing billable work for customers, to work on it for F R E E! Yes, I am quite crazy!

A couple of years ago my wife asked me if I could put together some sort of computer program to help with the capture of data acquired at the point of care in each clinic the practice runs. One that could also gather the pertinent data entered for a patient's clinic visits and create the report, i.e. clinic visit documentation for the clinic visit, from the data captured. Rather than the clinician continuing to have to dictate and a typist transcribe the dictation. With an EMR - Electronic Medical Record software program a MA - Medical Assistant can have the program create and print the report for the patient's chart and a copy for the referring physician if there is one.

My plan was to get some sort of EMR systems in to the practice so that my wife and the other Nurse Practitioners that follow their own and the doctor's patients, could escape from the office much sooner than the 7:00 PM or later that they had been electing to do in order to get the patient visit documentation completed. My wife starts at 6:30 AM! No, she isn't paid a six figure income. She is salaried! No, medical practices and doctors are not all rich. How many of you guys have a million dollars or so to spend or will commit to spending over three years in a lease arrangement, on a full blown Electronic Medical Records System and the necessary server, etc. hardware? Especially, if a good portion of your patients, 50% or so, of your practice, are on Medicare. Medicare is a government program that keeps cutting reimbursement rates every year to medical practices. Even though medical practice's operating costs keep increasing!

Given the needs of this medical practice, my choice was to create a database and associated application in Access because the practice had been buying their PCs with MS Office Pro that comes with Access in addition to Word, Excel, etc. Eventually the Access app developed would be "compiled" aleviating the need to have the full Access package installed on each PC. Only the ACCESS run time would be needed on each PC. This approach would alleviate the need to purchase an expensive database software package such as MS SQL, Sybase, or Oracle, etc. and a server to run it on. Plus a tremendous amount of programming effort to not only create the database but also the client side software that would access the data in the database. Not to mention my not wanting to nor being able to afford the cost to setup a system in my small, one person (me) computer consulting company's office to do the development. The practice has a Unix server for its aging text practice management system. The PCs are networked on the LAN and they have a fractional T1 based WAN between the practice's main and the one remote offices. So far the parts of the Access app I have developed, that is installed in the practice, and that is being used daily, meets the clinical staff's patient visit documentation needs and performs quite acceptably across the practice's WAN. So far a good choice of software to create their "mini" EMR app in, eh?

Now, the issue of duplicate data. I took the position that each clinic table would contain a "snap shot" of the data that is captured at each patient's clinic visit. That data is unique to each clinic visit and should only be stored in the clinic's patient visits table. I decided that each clinic visit's record should be a "snap shot" of that visit. Additionaly, the medication name the patient is taking, the diagnosis(ies) made, the Lab name that drew the blood and perform the analysis on it and provided the results, and the clinician's initials and full name that saw the patient in the clinic visit would also be stored in the patient's clinic visit record eventhough that data is also in other tables. Yes, that "violates" the Normalization rule for databases. However, a patient's medical records, in this case their clinic visit record, is considered a "legal document" and therefore, I content, must contain all of the data captured and entered in to the record for each patient clinic visit intact no matter what. Therefore, no matter what happens to data in the other tables through deletions, changes, etc., and yes that happens, the patient's clinic visit record stays intact. Quite frankly I would rather take the rath, as well as, the slings and arrows of other programmers railling against my violating database Normalization rules than have to admit to a lawyer in a medical lawsuit that the data in a patient's clinic visit record has changed from what was originally there because data was changed in another table in the database. In case you guys and gals don't realize it, you could be and would be held accountable for how you setup a database and its app in a lawsuit! Eventhough you did it as an employee of your or someone elses company. Welcome to the realities of the real world that the software we develop must exists in!

Well, back to work on my project. I have committed to completing it by the end of May. At the expense of peforming any billable work for customers. There is now just no more time for unnecessary posts.

Best Regards All,

 

[PHV]

Have you considered an append query (launched by DoCmd.runSQL or CurrentDb.Execute or with a Recordset) in the [Save Clinic Visit]'s Click event procedure ?
I guess you should already have all the necessary infos at hand for Tbl_Clinic_Visit_Vital_Signs when the new Tbl_Coumadin_Clinic record is saved.
As for the syntax (SQL code):
INSERT INTO tblName (numField, textField, dateField) VALUES (999, 'XYZ', #2006-05-10#)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fneily]

Since you mentioned HIPAA, you probably also heard of HCFA (HealthCare Financing Admin.), which is now called CMS (Center for Medicaid and Medicare Services). I was a programmer there for 4 years at the headquarters. As one of their head programmers and consultants, again I've never seen a clinic which uses an Access database, rationalize the way you do about relational databases and laws concerning retention of data. As mentioned, you can create a correctly design database, use "history" tables, etc. and not face the "legal" hurdles. But, then, of course, we only dictated the laws. I not sure of your interpretation.

 

[DPROMICS]

PHV -

Yep, on the "Vital Signs" Tab of the main form I am going to go with a SQL query to populate the list box to display the vital signs data collected for the currently selected patient at each clinic visit. Just need to figure out how to do a multiple SELECT query based on the currently selected patient. I have a couple of querys for reports that do part of what I need already.

Fneily -

Yep I know of HCFA . . . Ugh and HIPAA. I started out as a computer tech at a LARGE group cardiology practice. 35 doctors at a main and six regional offices spread around Southeastern Michigan. Over six years I was the guy that stayed the longest and ended up the Manager of Information systems for the practice. Until I left to move to Arizona.
I had a staff of three including me!

At a patient seen volume rate of over 100,000 per year at that practice Access just wouldn't handle the volume.
Access will suite the needs of the small group cardiology practice and its yearly patient volume.

Access also allows easily setting up graphing of data. That comes in handly when trying to "tune" a patient's Coumadin or Warafin medication doseage to achieve a desired INR (measure of how quickly the blood will clot) value. It is also handly when evaluating a patient's long term vital signs data trends. Its the old adage, "A picture is worth a thousand words".

Legal liability wise . . . my philosophy is, "An ounch of prevention is worth a pound of cure". Or a ton of lawyers and their legal fees.

Best Regards All,

 

[PHV]

how to do a multiple SELECT query
an UNION query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DPROMICS]

PHV -

Yep, You are correct. I will want to do a UNION in the query if I remember correctly. I need to go back and re-educate myself on setting up a query that pulls data from multiple tables, where there is a one to many relationship between one table to the rest of them.

I already have several querys that work correctly. They pull data from Aclinic's visits table for the currently selected patient. The patients table is setup aa a one to many relationship with the clinic visits table. So, I think most of the work is done. Its just tying them together to get data for A patient from the clinic visits tables where there are entries for that patient.

Then displaying the results in a list box.

Best Regards,