Logging changes

  • Thread starter Thread starter Steve G
  • Start date Start date
S

Steve G

I think I need some advice.

We have a relatively mature database with over 7500 records in the primary
table. One of the fields in the table is named status, and in this field we
record the current status of the patient. This field can change from time to
time, as a patient's status changes. We have a form that allows us to make
these changes as they are needed directly in the table.

We are now in need of recording when a patient's status changes, what it is
changed to, who changed it, and the date of the change. My thought is to
create a new table that will store this data but the problem I am having is
that I don't know how to get the data there from the form we use for making
changes to the primary table, without creating a sub-form with redundant
fields.

Any help or advice would be very much appreciated.

Steve G
 
Hi Steve,

It sounds like you need a new table: tblStatus to contain the status
information.

tblStatus fields: StatusID (Primary Key, Autonumber would work just
fine), Foreign Key which is the Primary Key of the parent table (Long
Integer if the primary key of the parent table is an Autonumber field,
otherwise, whatever is the Primary Key happens to be. (what is the name of
the parent table?). Next field is DateOfChange, a date field. Next is
TimeOfChange - I recommend 24 hour time if everyone there understands it.
Status, a text field - note that there could be a lookup table of all valid
status names. The next field is WhoChanged, a text field. This field too
could have a lookup table listing all of the authorized status changers.

By the way, I used the term lookup table twice. However, don't confuse
lookup tables and lookup fields. Lookup Tables are a good thing, they help
minimize data entry contributing to speed and accuracy in the use of your
application. Lookup fields it table design are dysfunctional traps and
should be avoided. They appear to give a neophyte application designer a
lot of result for a little action. However as your design progresses and
you add more functionality you'll have to go back and re-design your tables
properly.

If/As you implement the above changes you might consider how to get the
existing forms to display just the most current status record for this
patient's record on the existing form. Another note; if you keep the
existing form and do the foregoing display of status then I recommend that
you put code in the doubleclick event of the existing Status text box to
open a new form showing the relevant patient info and the status history.

In fact I recommend a rework of the existing form to display the
necessary patient information except status and that you create a subform;
sufStatus based on the new table. Access Help is very helpful about
creating subforms. When you do place your subform on the Patient/Main form
you'll want it to be toward the bottom of the form. You can create
sufStatus by selecting table Status in the database|tables window and
clicking the Autoform Wizard icon. It will step you through the design.
Save your form with the name sufStatus. Fiddle with the design of the new
form to get it down to a minimal form that will not have a header or footer.
Set its default display to "continuous forms". In its properties click the
Data tab and then build a query on table Status by dragging each field to a
column in the QBE grid.. In only the fields DateOfChange and TimeOfChange
click in the sort field in the QBE grid and select 'Descending' in both
cases. Exit the Query wizard and tell it that yes you do want to save the
changes and update the property and No, you don't want to save a query of
that name.

Back in form design, save the form.

Open your new Patient/Main form and you should have all of the data as
before and a status history showing every recent change up to the present.

You should be on your way. You'd be surprised how helpful Access (97)
help can be. Access 2000 Help is crippled. What version of Office/Access
are you using?

Lurk this newsgroup and microsoft.public.access.tablesdesign to see what
issues others are facing. Post back when you get stumped.

htl
 
Back
Top