move and delete records in one query

H

H0MELY

Greetings, I am building a large database that will work with between 1.5 and
2 million records. Previously when I wanted to remove a record from a table
but not delete it I would run two queries, a make/append table query and then
a delete table query. Due to the size of this database the queries tend to
take a while, I was hoping that there is a way to move the records to a new
table and simultaneously delete them from the old table. Any suggestions
would be greatly appreciated.
 
R

Rick B

Why do this? Just add an "inactive" field and exclude those records from
your queries.
 
H

H0MELY

Thank you, both of these ideas are great. I will do some testing but here is
the reason I had not used them previosuly. The data is given to me in a Text
deliminated file which I then import. I then run an SQL statement "ALTER
TABLE DROP COLUMN" to get rid of the unnecessary columns. The data needs to
be grouped into 9 groups based upon the data in one column. After the data
is grouped, the master table is reviewed to ensure we did not miss grouping
any data.

The database now becomes a tool for analizing the data. I found that if I
tried using only the master table when analizing the data, each time I would
open the form or datasheet...it would query the table. Because of the amount
of records it sometimes was quite slow. I decided it would be quicker to
just make seperate tables for each group. This is the reason behind my
original question.

Any ideas on speeding up the database would be greatly appreciated. I have
never created an Access database to hold so many records so I am somewhat in
unfamilliar territory. Oh, and because the database is actually not for my
use solely...I have tried to stay away from VBA unless absolutely required.
Again, thank you for the earlier suggestions and I will definately do some
testing. Other ideas are most definately welcome!! =)

-John
 
Top