Re: Designing a hospital visit database

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Mark,

I hope you continue because I am an RT who is learning to develop access
databases and I sure can follow the medical terms and how they relate to one
another better than, orders/customers/vendors/parts etc....Good Luck, I'll
be following your posts if you continue this.

Linda

"Mark S" <msimonian01@xxxxxxxxxxx> wrote in message
news:e0Eex6nMGHA.668@xxxxxxxxxxxxxxxxxxxxxxx
I appreciate such a thorough review of my considerations. My databases in
the past have not been as complicated as a true electronic medical record
must. That is why I bought one and did not build one. I am expert first at
medicine and only dabble in Access to get some routine data collection that
there was no specific tool to do.

The reason why the table was split is because of security. In previous
uses of a simpler database the primary table sat on a SQL server and was
maintained (secured) by the hospital and my front end was sitting on two
different workstations in the hospital. No data resided on the
workstations but was on a secure server.

I was asked to look at this because neonatologists (doctors who care for
premature infants) wanted to create a legible record the way I create with
my newborn reports they viewed when I sent them patients. THey knew that
systems that could do what I was doing could cost tens of thousands of
dollars and I might do something for them without any cost. (It is just
fun building databases) I am sure that I bit off more than I could chew to
do a professional job but some tool might allow them a much better
tracking tool and cleaner record than they had before and no dictation
would be needed.

Let's see if I can digest the comments and start putting this into action.


*****************************************
Mark M Simonian MD FAAP
681 Medical Center Dr West #106
Clovis, CA 93611
(559) 325-6850
www.markmsimonian.medem.com
"BruceM" <bamoob@xxxxxxxxxxxxxxxxxx> wrote in message
news:OTN$TslMGHA.3272@xxxxxxxxxxxxxxxxxxxxxxx
There are lots of twists and turns to a database of this sort. Each
visit could involve several providers, each provider's portion of the
visit could involve any number of tests or procedures, and so forth.
Thinking about a database is sometimes the trickiest part.
Back to Mark's original question, a pencil and paper sketch or flow chart
sort of thing may be useful as a starting place. Also, I don't see that
there is a reason to use a different database for some of the tables and
for the forms, etc. There may be good reason to split the database, but
that is a different matter.
Also, Mark, be aware that this is a public forum. Your e-mail address
and other information are now widely available. I recommend a fake
e-mail address, or one that needs to be parsed by a human, such as:
brQucWemE.RemoveCapsFromName@xxxxxxxxxxxxxxxx
(which is not only disguised, it is fictional)

"mnature" <mnature@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D64934EC-DF66-4A5A-BA7A-1876EB4B8A01@xxxxxxxxxxxxxxxx
You do make a good point, in that a visit usually involves one patient
at a
time. However, the point about a visit could be with someone other than
the
doctor themselves is good, since a doctor may wish to keep information
about
consultations or various therapists also. Each Patient Visit could
possibly
branch off to other tables, which could itemize special topics such as
diet,
therapy, consults, etc., which wouldn't have to always have an entry for
the
Patient Visit, but would be there to link to if necessary. So, you
could have

tblPatientVisit
PatientVisitID (PK)
PatientID
VisitID
DietID
TherapyID
ConsultID
LabWorkID
etc.

The VisitID would just be information that the patient's doctor would
gather, and other tables could be for other types of information
gathered at
different times than the doctor visit. They wouldn't have to be used,
but
would be available, probably as drop-down selections. That would be a
handy
way of filtering various types of information for forms and reports.

There are nearly always several ways of approaching a problem. The best
way
is to visualize what information will be needed from the database, and
then
plan the tables to make it easy to enter and filter the data. After the
database is created, you will need to enter data, and you will want to
retrieve data. Make it so that the database does as much of that work
as
possible.

"BruceM" wrote:

I think a junction table would be needed if each visit could have
several
components (exam, treatment, tests, etc.), or if each visit could be
with a
different provider (doctor, therapist, etc.), or if visits somehow
involve
multiple patients (workshops, etc.), or any number of other scenarios.
However, I don't think it is needed to associate Patients with Visits
as
such. Each patient could have many visits, but each visit is (I
assume)
associated with a single patient. Or am I missing something?
Wouldn't be the first time.

"mnature" <mnature@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F1E05D18-343C-4183-B1A3-9438F5DA3B14@xxxxxxxxxxxxxxxx
There are several types of information that you are looking at, and
there
are
ways to relate all of them.

First, you need patient information (stuff that doesn't change, like
name,
birthdate, sex, etc.), and you need information that you get during a
visit
(physical exam, vital stats, tests ordered, test results, etc.).
These
belong in two different tables.

tblPatient
PatientID (PK)
PatientStuff (all those little details, etc.)

tblVisit
VisitID (PK)
VisitDate
VisitTime
VisitStuff (again, all those little details, etc.)

However, you need a way to relate these tables. You can't do it
directly,
because you have many patients, and many visits. So you use a
bridging
table, that will match up patients with visits.

tblPatientVisit
PatientVisitID (PK)
PatientID
VisitID

You go into relationships, and match tblPatient.PatientID with
tblPatientVisit.PatientID, and then match tblVisit.VisitID with
tblPatientVisit.VisitID. This will form the relational link that you
need.
You can then put as much detail as you want into tblVisit, with links
to
other tables that detail various medical stuff that you want as
drop-downs
in
your forms. You can now filter for all of a patient's visits, to use
for
a
report or form. If you use subforms, you can step through all of the
different visits that relate to a patient.

See if this format will work for your needs.

"Mark M S" wrote:

I need some help with some basic design considerations to captures
variations of visits in a hospital of the same patient.

I am using Access XP ( 2002) and developed an application to capture
newborn
care - the initial history and physical and follow up visits. I did
this
storing the information in a single table. (I used many supprting
tables
to
describe each element of the exam but basically this was single
table
that
supplied the information for the daily report.)

I am sure that wouldn't work if I kept multiple variations of the
same
information for the same patient if I decided to record the
individual
elements on a daily basis rather than for one visit. I had completed
the
progress note before by creating a single memo field that store
daily
progress notes which were a narration that I typed and I pasted into
a
progress note memo field. I would just paste a new note at the end
the
the
prior so it would be one long string of text.

This design worked well until I decided that the same detail I used
for
the
History and Physicial was going to be necessary for the progress
notes
too
(which previously was a memo field that I just typed basic
information)

Now I wish that each follow up day I could implement the detailed
exam of
the heart and abdomen etc in dropdown choices for multiple days for
the
patient.

I imagine that I must change my design to add additional detail
visits.
It
is just unclear to me how I need to redesign my tables to store
multiple
encounters and so that I can query and report subsequent visits.

Now I have a base table in database0.mdb that that is linked to
another
database that has the foms, queries, reports, and supporting tables

Database0 has
FirstName
LastName
BirthDate
Length
Weight
Gravida
Para
Heart
Chest
HandP
ProgressNote
etc.

Database1
tblHeart(contains i.e. murmur, dysrythmia, normal) for the entry
form
tblChest (contains i.e. symmetric, retractions, etc) for the entry
tblGravida (1,2,3,4,5, etc) for the entry form
(forms, queries, and reports)

What information would you need to know to give me some guidance?

*************************************************
Mark M Simonian MD FAAP
Medical Director, ChildNet Medical Assoc.
681 Medical Center Drive West #106
Clovis, CA 93611
(559) 325-6850
www.markmsimonian.medem.com
****************************************
Alert: This email and any files transmitted with it
are intended solely for the use of the individual or
entity to whom they are addressed and may contain
confidential, patient health or other legally
privileged information. If you have received this
email in error please notify the sender by email,
delete and destroy this message and its attachments.
Any unauthorized review, use, disclosure,
or distribution is prohibited.












.

Relevant Pages

  • Re: Designing a hospital visit database
    ... There are lots of twists and turns to a database of this sort. ... Patient Visit, but would be there to link to if necessary. ... way of filtering various types of information for forms and reports. ... progress note before by creating a single memo field that store daily ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Table problem
    ... isn't the road to normalization and the whole process really has nothing to ... It is how you would proceed with any relational database. ... to a particular patient, for a particular visit, you could make some ... VISITS (VisitID, VistDate, patientID) etc -- all about visit, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: How do I set up Diagnostic centre Management database?
    ... You noted in your original posting that the database is for personal use, ... At a glance you would need a Patient ... which may be linked to the Examination Type table; quite possibly a Charges ... patients send by particular doctor in given period of time. ...
    (microsoft.public.access.gettingstarted)
  • Re: How do I link AND APPEND the linked data
    ... the database would have been to fully replace the hard copy data. ... we still maintain the old-fashioned patient charts and want to use ... field/column in excel. ... your append query. ...
    (microsoft.public.access.externaldata)
  • Re: Basic Database Design
    ... the Patient appears and I open my Database on the ... Each Insurance Company furnishes all the products, ... note that you can dispense with the CoServID primary key field, ...
    (microsoft.public.access.tablesdbdesign)