Re: How do I link AND APPEND the linked data

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

Dr. Lidor wrote:
Hi Dan,

Thanks for the very useful suggestions.

In regards to the ?temp? table and the ?append? query, I?m playing with this
option for quite awhile, but could not yet solve two major problems with this
approach:

1. The patient?s records are updated throughout their pregnancy (with new
findings or new lab results, etc). The Append query does not update existing
records but only add new ones. One possible solution is to delete all the
records from the current table with a Delete Query and use an Append query to
copy again all the records (after updating the excel table and the linked
access ?temp? table). I just don?t know yet how to write a code to do it
automatically. I could not figure out if the Update query could also do such
job.

2. The Append query adds the new records before (on top) of the old ones,
rather that after them. Is there a way to control where the new records are
added?

In regards to the suggestion about the pictures location ? it is an
excellent one. The only problem ? I don?t know much about VBA programming. I
just bought the book Access VBA programming for Dummies to comprehend in
details your code, though in general terms I understand it. If you don?t mind
I?ll ask for your advice in case of unexpected difficulty.

Thanks again for your very valuable suggestions. I feel now much closer to
the end of this project than ever before.

Dr. Lidor

Dr. Lidor;
There are two solutions I can offer:
[quoted text clipped - 55 lines]

Thanks, Dr. Lidor

--
Dan Knight
Knight Information Services
403-835-2234

Message posted via http://www.accessmonster.com
.

Relevant Pages

  • 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: How do I link AND APPEND the linked data
    ... The Append query does not update existing ... copy again all the records (after updating the excel table and the linked ... Second, and more importantly, is DON'T add the scanned picture directly to ... The scanned graphic file will be larger - ...
    (microsoft.public.access.externaldata)
  • Re: Importing Data from Excel, but...
    ... Be careful about the formatting in the Excel sheet. ... Create an Append query that uses the temporary table to update the Main ... you can do the data manipulation you need. ...
    (microsoft.public.access.externaldata)
  • RE: import from excel to access
    ... in string format to a date, in the Update To row of the query: ... > I faced another problem in the append query part. ... > But how can i convert the field type back to 'Date/Time' in the append ... >> import serveral Excel files each month. ...
    (microsoft.public.access.externaldata)
  • Re: importing date fields from excel
    ... Ken Snell ... But even when I import my Excel date field into a new Access ... Then of course when I create my append query, ... > formatting the Excel date field? ...
    (microsoft.public.access.gettingstarted)