Re: How do I link AND APPEND the linked data

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

Hi Dan,

Once again - great advice! I probably could have used it if the purpose of
the database would have been to fully replace the hard copy data. But in all
actuality, we still maintain the old-fashioned patient charts and want to use
the database for two purposes only:

1. To analyze on the fly our patient population (i.e. how many and who are
the teen pregnancies we have at any given moments in our program or how many
diabetes in pregnancy patients we have at 37 weeks, who they are, and after
deliver - what was their outcome, etc.).

2. To allow L&D nurses to tap into this electronic prenatal record database
to save on hard copies updates that are periodically faxed to them, and to
allow them to find available information on a patient for whom hard copy was
not yet been faxed.

The bottom line here is that there is really no importance to WHEN the data
have changed. To give you a practical example: suppose a patient enrolls at
13 wks. She gets full history and physical and certain routine labs are being
taken (CBC, Type and screen, STD screen, RPR, Hepatitis B screen, Immunity to
rubella etc.). Once all the labs are back, our database entry person would
enter them into the patient’s record. However, we get other prenatal labs
later on, such as diabetes screen (which we routinely do between 24-30 wks),
GBS culture (which we routinely do between 34-37 wks), etc. Every test has a
field/column in excel. The fields for which data are still unavailable at the
time of the initial data entry are marked as “maybe” (since they could become
“Positive”, or “Negative”, or carry a certain numeric value later on, when
the results are available). This allows the data entry person to pull out (by
filtering) for example all the patients that are at 31 weeks and their
diabetes screen is sill “maybe”. She will then pull out their hard copied
charts and update from them the proper values in the electronic records.

Thus this is a static sequential database, and not an incremental one. At
any given moment in time, certain patient records are already fully
completed, ready to be analyzed or to be opened by the L&D nurses when the
patients show up for labor, while the records of other patients are still
being built up.

Taken all together, I thought there might be one simple way to update the
final Access database: Since the linked “temp” Access chart is always
reflecting the changes made in Excel by the data entry person, a code could
be written that upon opening the “final/target” database table, would run a
Delete query to delete all the current records from this table, and then it
will run an Update query to fill up the table again with the “new“ records,
by copying them all from the “temp” table into the “final/target” one (of
course, only some of theses records would be new; others would be updated and
still others would be unchanged). In my mind this is the simplest way to
deal with it without worrying about were appended records are going to end
up, since the original order/structure is always going to be maintained
exactly as the data entry person entered it in excel, i.e. - new patient
records are going to be added always underneath the old ones.

Hope it is clearer now. What do you think about this approach?

Thank you so much for your help. It’s really great stuff.

Dr. Lidor

"Danok via AccessMonster.com" wrote:

Dr. Lidor;
Glad to be of help. VBA Programming for Dummies is an excellant 'starter'
book: If it is written by Rob Krum (sp?), then his writing style is perfect
for the position you're in; he takes you step by step and builds on his
previous information.

With respect to the appending vs updating of "changing patient data; I can
speak only as a patient...whenever my doctor brings my file into the
examination room, I notice that ALL the data is there. He simply adds today's
record onto of the last document. I suspect the same is true for your
patient's hard-copy file.

Likewise, develop your database to replicate this technique. When you need to
"add" or append new data include a "date-stamp" field. All this is is a
date/time field added to the structure of your data table and populated with
the current date when the append is executed. To do this you can either
include a date column in your excel file or simply add an additional field in
your append query.

Adding the date & or time column to your excel data means you can add varying
dates and times depending on the accuracy needed. In other words, if you need
to know that data X for patient Y was performed 20/03/06 at 8:32 AM and data
Y for Patient Y was performed 20/03/06 at 10:45 AM, then the data should be
in your excel table. However, if all you need is to know which record was the
most recent record added to your access table AND there will only be one
record per patient per append, then use the append query to auto-populate a
date stamp.

For example: in your append query design view, once all the regular fields
from the excel data are in the grid add an additional field Updated: date()
or
Updated: now()
What this does is create a new field in your query named Updated and
populates it with either the date or now data. Date() returns the current
system date, Now() returns the current system date and time. I'd only use
Date() if there will only ever be one record per patient added per day,
otherwise I'd use Now().

This technigue retains all the history of the patient and can easily be
filtered or sorted to show only the most recent data by filtering for certain
date ranges using Between And filters or using the Max() or Min() functions.

I hope this helps.

Dan Knight
Knight Information Services
.

Relevant Pages

  • Re: Designing a hospital visit database
    ... Thinking about a database is sometimes the trickiest part. ... doctor themselves is good, since a doctor may wish to keep information ... Patient Visit, but would be there to link to if necessary. ... way of filtering various types of information for forms and reports. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: How do I link AND APPEND the linked data
    ... With respect to the appending vs updating of "changing patient data; ... your append query. ... Adding the date & or time column to your excel data means you can add varying ... I don?t know much about VBA programming. ...
    (microsoft.public.access.externaldata)
  • 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: 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)