Appending Tables

[Sheri Emery]

Hi,
I want to append old records to a "historical table", so
I've created a Query and used the Append option. I'm
running into a bit of a snag though - I want to keep the
most recent data for each client in the current table, but
I'm not sure how to specify this in the query criteria.
Basically, whenever there is an update, I want the old
records for that client in the historical table and the
updated data to appear in the current table. I want to
use this option so that my reports grab the most current
data, but we also have a historical record of when changes
occurred. I really appreciate any assistance you can
provide.
Thanks,
Sheri

 

[Lynn Trapp]

Sheri,
Why doesn't appending the record to the history table not do what you want?

 

[Sheri Emery]

Hi Lynn,
It does give me the historical record, but I need only the
current status to appear in other reports, so I need to
keep the current data for each individual in a "current"
table, and only move historical data. I want one current
record for each client only. Thanks for your help.
Sheri

 

[Lynn Trapp]

Well,
You should have a form for entering and updating client information. That
should always contain the current information. In the after update event of
your form run an append query to append the current record to the history
table. Initially, it will be the same as your current client information,
but the next time that client record is updated, it will be previous data.

 

[Guest]

Lynn,
Thanks for the advice. Will this procedure get rid of the
old record in my current table? I can only have one
record for each client in the current table. I initially
set up a command buttom that would run the append query
for the record and then delete the current record;
however, that command was deleting both records from the
tables specified in my forms (in my example, it was
deleting the client record and the supervision record).
Thanks again,
Sheri