Delete records moved to multiple tables / Access 2000

[TahosaMan]

I have tables with relationships set up to track membership. I need to be able to move inactive members and their data in the other tables to a duplicate set of tables in case they become active again I can move them back into the active tables.
I can append all of their data to a single table and then delete it but would prefer to have the inactive information appened in duplicate inactive tables.

As I have it now;
Active Personal Information Table
Active Membership Table
linked as Enforced Ref Int, Cascase Update Related Fields, and Cascade Delete Related Records

When I append/delete it all goes to a single table, Inactive Table All

But want it to go to;
Inactive Personal Information Table
and Inactive Membership Table

I'm fairly new but can use design and can understand some SQL.

Thanks,
Steve

 

[BNPMike]

I'm not sure how you are doing this but why don't you always first append the parent record (Personal?) to your archive before you append the child record (Membership). If the Personal record already exists in the archive then it will just get discarded.

It seems that your archive is only one table even though you want two tables, mirroring the live system. If that is true why did you do that?

 

[dgrainge]

Why do you need more than one table? Wouldn't it be simpler if you use a logical field to flag each account as active or not? And therefore use a query on the flag when displaying/editing active records instead of the raw table?

This might have another advantage if you had related data in other tables such as old order details - you wouldn't need to wipe them (assuming you have proper referential integrity). You could leave old stuff on file and it will all re-appear after making a single tick.

 

[TahosaMan]

I do run the append first but can only figure out how to get all of the data to a single table (Inactive Table All). I have an ACT_INACT field in the Active Personal Field Table that I key off of and then delete the record from the Active Tables. Once I get this to append to all three tables I can then reverse it to restore a person when they become active again (deceased members will never need to move back to the active tables).

BNPMike, It is current in test mode to see about appending/deleting records. I haven't been able to figure out how to append the appropriate data to the inactive tables.

Dgrainge, I could keep everything in the Active Tables and just use the flag (A or I) but we currently have 2500 records and only half that are active. Sixty percent of the inactive ones will never become again but may want the original information in 10 to 20 years when their sons come into the program.

 

[BNPMike]

I'd do what Dgrainge suggests. Instead of deleting them just mark them with a deleted flag. As long as you view/update them with queries you'll never get bothered by the dead records. Every year unload all the deleted data from each table you have into an archive image. As you're only doing it rarely you can take time to get it right ie make sure you pick up undeleted references where necessary.

Don't worry about the numbers of records until they're in the tens of thousands.