Archiving Data (1 Viewer)

[Tina49]

I have a database with 14 tables. Periodically, (like once a year) we like to archive the data into an archive table (within the same database). This works well if the person we are archiving has data in the main related table. However, the issue I am running into is if we have an employee that we want to archive; however, they don't have data in all the related tables, then the append doesn't seem to work. I mark the record for archival, then run the append query. It simply says you are about to append 0 records. If I fill the form out with bogus data, which allows all the tables to have at least one piece of data, then it archives fine. Why would this be?

I have checked and the query simply isn't picking up the new employee (for testing purposes I have named him Fred Flinstone. He resides in my employee table. My query calls for all fields from that table to be archived, if the archive field is set to Yes. His is set to yes.

 

[stopher]

I have a database with 14 tables. Periodically, (like once a year) we like to archive the data into an archive table (within the same database).

Why do you want to do this? It would be much easier to just have an extra field to show a record as "archived". It doesn't need to be moved anywhere.

 

[Tina49]

They simply don't want to keep the database filled with people that no longer work for the company. Also, they do not want non-employees showing up in reports. I we add an extra field to show the record as archive, (which we do have), then all the queries will have to be edited to have do not include archived. Which isn't a big deal for the 100 or so queries that I can see, but how do I access all the built-in queries that you don't see?

 

[stopher]

Hi Tina

I we add an extra field to show the record as archive, (which we do have), then all the queries will have to be edited to have do not include archived. Which isn't a big deal for the 100 or so queries that I can see, but how do I access all the built-in queries that you don't see?

One trick to get over this is to do as follows:

• rename your main table to something else e.g tblEmployeesAll
• create a query that only lists non-archived records i.e. filtered. Make the name of this query the name of your original table
• create another query but this time named after your archive table but based on records from your main table that are "archived"

By doing the above, all your current queries should still work normally - the only difference being that they will be using a query as the data source rather than the main table.

Not sure that will solve your original problem though. Maybe you can post your database (with made up data).

Chris