Archiving Data

[djeverett01]

I am working on a project that keeps track of all open positions, applicants, selectee, and other info about a position. What I need to do is be able to archive the position when the hire date of the selectee has been filled. The only problem that I see is that all of the information is in about 4 or 5 tables. Would you make a duplicate of each table and then run an append query to each table to archive the data or would you create one big table to haold all of the info from all of the tables? I need to be able to retreive this data also. Please help if at all possible.
Thanks

 

[JaneC]

Hi djeverett01,


Don't know if this is any help but why don't you consider doing it another way ? Why not add an Archived (Yes/No) field to the main table and then use this to filter out current items in your queries/forms ? Or, you could use the Hired Date to identify "active" positions.

Jane

 

[UncleJack]

Jane's suggestion has merit unless you're concerned about table size and network performance. I had a similar task for a food distribution organization with quite large tables running 15 stations on a network. I opted to create mirror images of the relevant data tables and used append and delete queries to cut the data from the "live" tables into the backup tables. Restoring or otherwise recalling the information is just the reverse. It can be quite a few queries, but the overall task isn't exactly rocket science type programming.

You could also combine the two techniques. For example, keeping listings less than a year old in the "live" table with a flag to show their inactive status. Anything older than a year could be dumped to the archive tables.

Uncle Jack