moving entries in a table

[ProCad Jen]

I have a database with a main table called "Tracking". This keeps track of
all my orders and projects. Any order/project that is over 3 years old needs
to be moved to another table (let's call it "Historical Tracking").

I need to build a query that will
first, search for anything older than the date input;
amend the found items to the new table;
delete the found items from the original table.

I'm guessing it may have to be severl interrelated queries, but I'm not sure
how to set it out.

Can someone help please?

Jennifer

 

[ProCad Jen]

After writing this post, I acutally answered my own question and figured it
out. sometimes you just have to trust you actually know what you're talking
about.
Thanks anyways.

Jennifer

 

[Evi]

An Append query filtered to < your date appending to your Historical Table
and a Delete query for that same criteria to get rid of the fields from your
Main Table, should do it. Keep your table in the same database if possible
and link any tables containing the Foreign Key fields to your Historical
Table as well as to your original. By doing it this way, you can make a
Union query which spans both your main and your Historical table and compare
data over several years.

The field you won't be able to append to your Historical Table is an
Autonumber Primary Key field from your main table but you will need some way
of ensuring that your records are never appended twice.

I suppose (without trying it out) you could have a field in your Append
query which equals the Primary Key field and append that, making the PK
field in your Historical Table a normal number.. You would have to remember
not to Compact your database after deleting though, until you enter another
record, otherwise the PK in your newly emptied Main Table will reset itself
back to 1.

The way I use, is to have a DateEntered field in my Main Table which has the
Default Value of Now() and is formatted as dd/mm/yyyy hh:nn:ss so that it
records the exact time that a record is entered. No-one ever sees this
field, it just works in the background (but has got me out of a few scrapes
before this)

In my Historical Table I have set a Unique Index for this field, the same
format as the Main Table but no Default Value.

This method, however, would fail if several records at a time are ever
appended to your Main Table. I'm sure that you will get a lot more
suggestions. My main one is always back up your database and keep not just 1
back up but several numbered backups.

Evi

 

[ProCad Jen]

Hi Evi,

This is pretty much what I did - first append and then delete. Do you do
these as separate commands? or have you figured out how to link them together
so that you do the append then the delete based off a single date input?
I've set up both queries into a macro, but I still have to enter the date
twice (once for each query) which leaves a lot of room for error.
Considering the client's discount is based on whether or not they have
purchased in the past three years, if I mess up the delete I'm causing a
possible feud with a client - something I don't want to get into thanks!

Jennifer

 

[John Spencer]

You do need two queries to run to do the Append and then the delete.

You can get the date one time by using a form with an unbound control to
enter the date and a button to execute the two queries.

Your criteria for the queries would be
Field: SomeDateField
Criteria: <= CDate(Forms![Your Form Name]![The Name of the Control])

You need to be careful that the DELETE query only removes records that have
been successfully transferred by the append query. How you determine that
the records are in the archive table depends on your structure and primary
key fields.

A better way to handle this situation would be to keep the old records in
the same table, but add a field to record if they are current or not. You
can then use the field to determine which records to use. I usually use a
date field to mark the date that the record is no longer active. When I
want only the active records I just check that the InactivatedDate field is
null.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..