Archiving to different table - service activities

[vangogh228]

There are many threads on archiving... many of which involve VBA code and are beyond me. Many others address moving the data to another database, which is also not what I need to accomplish in my small application.

I am tracking five customer annual service activities. Each year, these activities are to be performed, and the service rep enters the Activity Type, Date, and their Initials into the customer activity subtable (1-many with the customer data table) when each service activity is completed. I am writing queries to access the data from the activity subtable and then display the entered activity dates in the appropriate fields on the client record form (display only). ((The fields for input into the subtable and the fields that display the information will probably be on the same form.))

At the end of each contract year, the service rep is to go into the customer data form, verify the completion (or not) of the five individual service activities and then archive the customer service data to another table... SO THAT the displayed dates blank out and the process begins again in the next contract year.

So, my question is this: How do I take records from Cust_Serv_Activity_table and move them to Cust_Serv_Archive_table? I would like to have a checkbox on the customer form that indicates the process is complete, then a button that archives the service data, removes the check from the box and refreshes the form.

There is only one trouble with this: I have no idea how to do it!!!

I think I can write all the queries needed to display the data I want, but I don't know how to complete the process of the archiving. Any help is GREATLY appreciated!!!! Tom

 

[Rich]

Why move the data, unless it's a large recordset just adding a yes/no field and filtering the data via a query is much easier

 

[vangogh228]

Well, each year there will be new entries made into the fields. At the beginning of each contract year, the fields need to be blanked out after being given a final checkover. I am not sure if I know how to write a query to find data in a table that would be the "last entry of a certain type, unless I have archived it within the table."

In other words, Activity1 will have an entry made each year, with a date indicating when it was completed. Once they reach the contract start/renewal date, the CSR goes in, inspects the activity that was done (or not), addresses any issues on an ad hoc basis, then needs to blank the fields for the upcoming year's activities. I don't know how else to do it than move the data to another table.

Am I thinking incorrectly? To facilitate my idea, I was going to create a subform that has an input area for the data, and individual fields for the Activities on the main form that show the date of the entry, until it is blanked out by moving the data off the table.

Thanks for any help, Rich. You have been a great help in the past... and hopefully you can get me through this as well. All others are welcome to contribute!!

Tom

 

[vangogh228]

Any help here, folks? I really need to get this done. Any help is GREATLY APPRECIATED!!!!

 

[Rich]

Just add another field to the table, call it Archive, if you like ,define it as a yes/no field, add the checkbox to the form, in the underlying query for the form add the field and set the criteria to No, archived records will no longer display

 

[The_Doc_Man]

What you are perhaps missing is that if you simply include a date in the record, you can filter on the date's age and still keep your archive in the same table. At the beginning of the year, you write a query to set an [Old] flag to TRUE where the [EventDate] is earlier than (less than) the critical date at which you do your yearly turnover.

Then whenever you have an entry to make, create a new one with the current date. Make the query that drives your form filter out anything with the [Old] flag = TRUE. Now there is nothing to move.

But if you REALLY, REALLY have to move it, you copy the data table to another table with the same name plus "_Old" tacked onto the end. Then write an append query that copies table.* to table_old WHERE [Old] = True. Then write a delete query that erases table.* WHERE [Old] = True. Then write a macro that does OpenQuery on the append followed by the delete. Now run the macro once per year.

 

[vangogh228]

GREAT IDEA with the 'old data' flag.

Just gotta get back to basics sometimes!!

Thanks SO much!

Tom