Preserving Historical Data

[lexiwynona via AccessMonster.com]

Is there a simple way to preserve historical data and then recalling it for
weekly reports? I maintain a database with approximately 250 items that are
in various steps daily (initiate, plan, oversee, complete, closed). I need
to have weekly status reports that compare the data from the previous weeks.
(i.e., how many items were still open, in plan step, completed, etc.). I
can save the database weekly, but then it is a large file and it would be
harder to access that historical data. Is it better to have a Linked Excel
table that I can export the updates to weekly and then recall them for charts
and graphs?

Hopefully this makes sense.

Lexi

 

[Jerry Whittle]

IMHO the best way to archive data is to put a Yes/No field in the original
table named something like Active. When you don't want to see that record
again, uncheck it. Run all of your queries where you only want to see active
records with the criteria of Yes (no quotation marks) against the Active
field.

Reasons?
(1) You don't have to worry about moving records between tables.
(2) You don't have to worry about duplicates either in the Archive table or
between the two tables.
(3) If you ever need to search for both active and inactive records, you
don't need an inefficient union query.

About the only reason to archive data is because the database file is
getting near the max size limitation. If that happens you'll need to move the
data to another .mdb file anyway. Might be time to look into another RDBMS
which can hold more data.

One more thing about archived data and I'll get off my soapbox: Archived
data is lost data. Somewhere along the line someone will make a change to the
database which will prevent moving the archived data back into the table
without a lot of work. Seen this way too often. My worse case was data saved
to a tape backup system. When needed we no longer had that kind of tape
backup machine! Once we found on, many of the tapes has unrecoverable errors.
So much for archiving data.