How can i set up data to auto transfer to a different table upon .

[Lisa blair]

I have a student database with current students and when I type "Archive" in
the status column I want the record to transfer to the archive file and
delete from the current records file.

 

[DB Queen (Not)]

To put it in female terms I am trying to say - If Status="archive"then move
record to (table "Archive")

 

[Rick B]

There is no need to move it. In your reports, queries, and forms, simply
exclude any records with the status of "archive". It is not normal database
practices to move data from table to table.


Rick B

 

[Eduman]

Seeing this post, I have a followup question. I have a student discipline
database that was becoming large (for our standards) so I created an archive
table and a macro that did an append and delete query to the tables. If not
archiving, how do you get around having to go through 8000 records to view
more recent ones? Many of the teachers who use my database are not Access or
general database literate (no offense to them as they have told me this). If
I setup a filter or allowed the menubar filter to be present, I'm afraid
there would be corruption problems and accidental erasures/changes to
existing records.

 

[Rick B]

You would add an "archive" field to the table. Or an "inactive" field. Or
you could include a date in your records.

Then, any form, report, or query would have the criteria [Archive] = false
or [Inactive] = False or [DisciplineDate] > Date()-365

Do not set up filters or make them do it, simply include the criteria in
your query. In other words, the form NEVER displays the old records. You
don't have to move them to another table, you simply mark them as "old".

Furthermore, these "discipline" records would most likely be tied to a
particular student. If that student is flagged as "inactive" or has an
entry in the "graduation date" or similar, then their records should not
show up anywhere.

You never want to build a database that requires you to go in and clean
stuff up all the time. Use the queries to determine what data you are
looking at.

Of course, there will come a point when you may want to get rid of old
stuff, but my personal philosophy is that if you will need it for any
reason, keep it out there and flag it as old. The only time I take it out
of the table is when it is so old that I no longer need it, and then I
simply run delete queries.

Rick B

 

[Rick B]

I guess the question to ask is, "Why did you archive it?" Is it because you
may need to use that data from time to time? If so, wouldn't it be nice to
pull the archive data AND the current data in those instances?

Doing it as you currently do would not allow that. If you ever needed to
evaluate current and archive data, you'd have to run multiple reports.

Rick B

 

[John Vinson]

On Tue, 5 Apr 2005 05:31:05 -0700, "Lisa blair" <Lisa

I have a student database with current students and when I type "Archive" in
the status column I want the record to transfer to the archive file and
delete from the current records file.

As Rick says... don't. Just use a Query to display the archived or
non-archived records.

If you have so many records (and we're talking about 10,000,000 or
more) that you need to archive for performance, then you may not want
to be using Access for your data in any case.

John W. Vinson[MVP]

 

[DB Queen (Not)]

Just a note to say thanks to all you guys who helped me with this. Really
appreciate it. Will play around with it today and hopefully get the result I
am looking for.

 

[Chris]

Hi,
I agree with the idea of having a status flag/indicator on the table to help
with the query.
Regards

Chris
(I have developed a similar application in VB if you are interested)
Have not posted the domain name as not sure if I'm allowed to do that on
this newsgroup, do not come here often.

 

[golfmancan]

Hi,
I agree with the idea of having a status flag/indicator on the table to help
with the query.
Regards

Chris
(I have developed a similar application in VB if you are interested)
Have not posted the domain name as not sure if I'm allowed to do that on
this newsgroup, do not come here often.




Have you looked at Teacher's Partner. It is educational software that is far superior to E-teacher. you can find it at http://www.leadinged.com