Moving all info from one table to another

D

DPete14

I have a database to keep track of our company's projects. Two of the tables
within the database contain projects we have worked on: one for just the
current year, the other for all projects in our history. At the end of the
year I would like to move the current year projects into the history table.
All of the fields are the same in both tables. Any ideas how to do this
"automatically"?

BTW, this is for Access 2000.

Thanks, Dan
 
L

Lynn Trapp

Why move it? Just add a field that designates that the project is completed.
Then your queries can pull only those records where that field is set to no.
That way the data will still be available if you need to view it.
 
F

fredg

I have a database to keep track of our company's projects. Two of the tables
within the database contain projects we have worked on: one for just the
current year, the other for all projects in our history. At the end of the
year I would like to move the current year projects into the history table.
All of the fields are the same in both tables. Any ideas how to do this
"automatically"?

BTW, this is for Access 2000.

Thanks, Dan
Click to expand...

Lynn Trapp's suggestion is best, but if you absolutely must move the
data to the History table, create an Append query, using
Year([Datefield] = 2004 as criteria and it will add the 2004 records
to the History table for you.
Then use a Delete query, with the same criteria, to delete those same
records from the current year file.
 
H

Hem13P

I have the same problem. I need to move data from one table to another on a
date trigger. I also need the same movement of data activated by a button on
the switchboard. I have a training database and need to transfer data from
the 'forthcoming training' table to the 'completed training' table once the
training date has passed. However it would be too time consuming to use a
tick box and have to go through every record ticking them off. Help!! Thanks
 
R

Rick B

Why? You can search on the date. If the date has passed, son't pull it to
your "upcoming training" queries, forms, and reports. If it has not passed,
then pull it.

You never need to 'move' data between tables in access if you have a way to
test the data. That is not normalized design.

Another common example is employees. Lots of people ask how to move
terminated employees out of the active table. The answer is, "You don't!"
You simply add am Active or inactive ticlk box and use it as criteria when
pulling employees. You could also add a TerminationDate field and pull
records based on whether or not the field has an entry.

Rick B
 
H

Hem13P

I understand and accept what you are saying. It makes perfect sense. However:

I have managed to create the append and delete queries. This way people can
constantly view up-to-date reports which are opened through the switchboard
manager. I no longer have to worry about ticking off those who have completed
training to ensure they don'y show up in the forthcoming training report. So
basically it will make it easier for the people who do not know how to use
access to extract up to date reports without having to enter into the actual
database, where they will probably start causing a mess!!

I'm sure there is an easier way of doing this? As you can see I don't know
much about access and am working ways around problems instead of solving
them!!
All I need to do know is work out how to get the macros to run in the
correct order (ie. moving data before deleting data)??? Any ideas?

Thanks for your help, its much appreciated
 
Top