Viewing old records occasionally

[Guest]

I'm not sure where to post this but if I made up a topic it would be
application.design. I have records for a homeless shelter in an application
that I wrote 2 years ago just to help me keep track of things; it's about a
medium on the complicated index. I look up info in forms & reports on a
daily basis and have accumulated about 2,000 records for 4-5 yrs; the querys
and things are beginning to bog-down with all the records. I would like to
'archive' some old records so that it will run faster in the day to day, but
once in a while I need to look up an old record. I was hoping someone could
guide me as to methods of doing this. I tested an idea which was every time
I do an update (once a month) I could have other tables called 'old data' and
copy the records that were more than a year old to them. Then when I needed
to look at an old record I could do some union queries with the current & old
data and then use them in copies of the same queries I use day to day. It
seems to work on a simple model but it seems aukward also, not in the using
but in the execution and writing. Am I on the right track or totally off
course?? Thanx

 

[tina]

well, 2000 records is not much for an Access table - unless each record
holds a very large amount of data. as 4-5 years of data, you're only adding
400-500 records a year, or less than 2 records a day - very few indeed.
before you think about separating data into "current" and "archive" tables,
suggest you review your tables design - specifically your use of indexes. a
table that has properly indexed fields can be searched by Access much faster
than one that does not. you can read up on Indexes in Access Help, and i
have no doubt that there is plenty of information about Access table indexes
on the internet - try doing a Google search on these newsgroups, as well as
a general internet search.

hth