Auto archive old data on update in MS Access?

[goldriverdancer]

Dear all,

I'm not sure if this is possible or not, but I'd like to make a form which allows automatic archiving of old data when I update the form field.

I've thus far created a simple Access database to keep track of all the pumps my (mining) company uses. For example, I have a pump that is operating from the 21st of April. Then, it fails on the first of May. I would like to allow a user to enter new data into the form (change pump status from operating to failed and the dates) to display the current status, but not lose previous information by storing it in another table.

Then, if anyone should want to call up the history of a particular pump, they can just look it up in the archive table, and all its past failures and startups can be found.

Can someone help me with this?

Thank you very much in advance,

Stef the Noob

 

[pbaldy]

Most of us would recommend not moving the data into an archive table. It sounds like you already have a field that would signify the pump's status. Leaving the pumps in the same table enables you to query active pumps, failed pumps, or both by simply changing the criteria on a query of that single table.

If you really want to archive them, you run an append query with the appropriate criteria and then a delete query with the same criteria. That said, I would only do so if the quantity was massive (tens if not hundreds of thousands).

 

[Galaxiom]

Most of us would recommend not moving the data into an archive table.

Ditto.

Perhaps I am paranoid but I really don't like archiving happpening as an ordinary user action. Things can go wrong resulting in the delete without the append. Much safer archiving as an administrative function after a backup and with no users in the system.

Much of the desire to archive comes from pessimistic expectations of the capacity of databases to deal with vast numbers of records.

 

[goldriverdancer]

Thank you very much all for your replies. Based on your advice, I think I might not try this auto archive move after all.

However, do you have any good suggestions as to how I can go about updating the status of the pump without losing the previous data? I know I can query to find out which pumps are currently active and which are failed... but I would like to be able to look into the history of one particular pump.

For example, Pump 1 has been operating since 1 jan 2009, and between now and then, it has failed twice, but got fixed and started up again both times, and is currently operating. I would like to access its history (how many times its failed, when, why and so forth) easily, not just see its current status.

That's why i wanted to auto archive it, so that people could just put in new data into a form but still analyse its previous performance. Previously, the old database had a few records for one pump: Pump 1-1 and Pump 1-2, everytime it failed and started up again, there was a new record for it. I found this not very efficient and was trying to improve it.

Any ideas? Thank you very much.

Stef

 

[Galaxiom]

I would use separate tables for Pumps and InstallationSites.
A join table would record the PumpID, InstallationSiteID and the dates when put into and taken out of service at that site.

The user would normally query the Installation and get the related records for the currently in-service pump but could check a box to see the history of all pumps used at that location.

This would also allow Pumps to be kept track of when moved to different Installations.