Help Accessing Archived Data

[rookiedev]

I need to do some archiving of records that have a great deal of associated data. For example: Each Customer has Workorder records and each Workorder has Labor, Parts and Billing records. My concern is that I do not know how to get all the associated data archived properly for the Workorder records for a given period. Can anyone point me in the right direction? Here is the other thing. Not all Workorders have billing records. Is there any way to make sure I'm not missing anything?

My other concern is this: There are times that I am asked to do history cost reports that require me to use both the current information and the archive information to get a total value. If I have this archive information in a different database will I still be able to do the reports accurately?

I realize there are alot of IF..And..Or..buts..with this however, I have never done this before and it is a bit overwelming.

Does anyone have anything to offer?

RookieDev

 

[Darrylles]

Hi,

2nd concern: you can create 'link' tables in db1 to your archive tables in db2 - so no problem with accessing tables from db2 in db1.

(To create a 'Link' table: click New->Link Table, you are prompted for db name and table name).

1st point: you need to import all relevant tables from db1 into db2 (structure only - no data).
Then you create link tables to all of them as described above.

You'll then need append queries to copy the records from all relevant db1 tables to db2 (archive) tables. You'll need to use the defined relationships to make sure you maintain the associated data.

You'll then need to run delete queries to delete all archived data.

Make sure you back-up the entire database before attempting this - you will probably make the odd blunder.

Please re-post for specifics.

Kind Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[rookiedev]

Darrylle-
I did as you advised and I was able to view the archived records but only after I copied and rerouted all the forms and subforms associated with viewing the data. Honestly it seems as if I added more to the database rather than reducing it's size just because I had to copy all the forms, subforms and queries to be able to view the information. Should I have placed the forms and such in the database I created and linked to that somehow?
Thanks!
RookieDev