I think archive tables are a good idea, and have several of them at different clients. Write a routine where the user will type in a transaction date, and you archive off records. Of course alot of error checking before you then delete the records from the main table.
You will need to make a copy of your edit forms and reports so one is based on main table, and one on archive table. Or you can make your reports always based on temp tables/views and populate them from either main or archive. Make sure you maintain FK relationships on the archive table as you do on the main table. Sometimes this can slip through the cracks, and the user would be able to delete an account code in the account master table, thereby "stranding" a record that's in the archive table. Karen > Is there a preferred method to archive data when a table starts getting > large? > > I have indexes on the commonly searched columns but queries still seem to > be getting slower as more data is added into the table. > > I was thinking of making it into two tables. > > One table of data that is used frequently. > > One table for historical purposes. > > > > Is there something else to consider? > > >
