Archiving data

[Paul H]

Could some one give me some pointers on basic archiving techniques? I have
developed several databases but never been faced with this issue. Here is
the basic scenario..

Suppose I have the following two tables tblCustomers and tblOrders. I want
to archive all orders over two years old.

Is this how it is done?:
Archiving:
----------
I run a query to move all matching rows from tblOrders in the main.mdb into
the tblOrders in the archive.mdb.
I put the current date in the ArchivePerformed field of tblArchiveDate to
date stamp the event.
Querying archived data:
------------------------
If the following query is run by a user:
SELECT tblOrders.*
FROM tblOrders
WHERE (((tblOrders.OrderDate) Between #1/1/2000# And #1/1/2006#));
Should I only show non-archived results and warn the user that archived data
exists that matches the query?
Should I get clever and pull all the data together into a temp table and do
stuff with it?

Am I way off will how to set up archiving and build a UI for it? How do you
implement and manage archiving?

Paul

[Terry Kreft]

You talk to the customer and see how they want it implementing.

The point of archiving is to reduce the number of records in the table and
so increase performance, so the likelihood of the customer wanting you to
automatically pull records from the archive is low as this will hit
performance, although they will probably want a method of reviewing or even
restoring archived records.

The usual things you want to do are
Transfer data from the live tales to the archive tables
Delete/Mark as archived the records in the live tables
Update your archived diary.

The best way is to use a transaction so that either it all works or none of
it works.

--

Terry Kreft
"Paul H" <no****@nospam.comwrote in message
news:Q6********************@eclipse.net.uk...

Could some one give me some pointers on basic archiving techniques? I have
developed several databases but never been faced with this issue. Here is
the basic scenario..

Suppose I have the following two tables tblCustomers and tblOrders. I want
to archive all orders over two years old.

Is this how it is done?:
Archiving:
----------
I run a query to move all matching rows from tblOrders in the main.mdb

into

the tblOrders in the archive.mdb.
I put the current date in the ArchivePerformed field of tblArchiveDate to
date stamp the event.
Querying archived data:
------------------------
If the following query is run by a user:
SELECT tblOrders.*
FROM tblOrders
WHERE (((tblOrders.OrderDate) Between #1/1/2000# And #1/1/2006#));
Should I only show non-archived results and warn the user that archived

data

exists that matches the query?
Should I get clever and pull all the data together into a temp table and

do

stuff with it?

Am I way off will how to set up archiving and build a UI for it? How do

you

implement and manage archiving?

Paul

[David W. Fenton]

"Paul H" <no****@nospam.comwrote in
news:Q6********************@eclipse.net.uk:

Could some one give me some pointers on basic archiving
techniques?

No, because I've never archived any data in any of my applications.

Why would anyone ever do that? If you have so much data that
Access/Jet chokes on it, then you should migrate to a different back
end.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Mark Reed]

Have a read of Allen's site about the topic:
http://allenbrowne.com/ser-37.html

Mark

"Paul H" <no****@nospam.comwrote in message
news:Q6********************@eclipse.net.uk...

Could some one give me some pointers on basic archiving techniques? I have
developed several databases but never been faced with this issue. Here is
the basic scenario..

Suppose I have the following two tables tblCustomers and tblOrders. I want
to archive all orders over two years old.

Is this how it is done?:
Archiving:
----------
I run a query to move all matching rows from tblOrders in the main.mdb
into the tblOrders in the archive.mdb.
I put the current date in the ArchivePerformed field of tblArchiveDate to
date stamp the event.
Querying archived data:
------------------------
If the following query is run by a user:
SELECT tblOrders.*
FROM tblOrders
WHERE (((tblOrders.OrderDate) Between #1/1/2000# And #1/1/2006#));
Should I only show non-archived results and warn the user that archived
data exists that matches the query?
Should I get clever and pull all the data together into a temp table and
do stuff with it?

Am I way off will how to set up archiving and build a UI for it? How do
you implement and manage archiving?

Paul

[Paul H]

Thanks folks, all very helpful answers...I need to talk to the client now.

Paul