Automatically expunging data to a new database

[adrift]

I've got a database of active patients with several tables. I want to be able to add a discharge function that automatically transfers discharged patient data to a new database and deletes data from the current database. This would include all data that matches the patient identifier.

The discharged patients database would have identical table design as the current database.

Is there code that will scan each table, pull out relevant records and transfer them to a different MDB?

Thanks, Shawn

 

[MichaelRed]

Almost certainly not any such 'code' already developed.

Certainly such code COULD be developed if the db Schema were known.

Far more certainly, it is not a trivial task to be just cobbled together on an ad-hoc basis. My FIRST thought is to ask if ALL of hte fields w/ the "KEY" value have the same Field name and DATA TYPE. If so, a routine could just itterate through the tables collection, checking the key value in the field. Generate SQL statements to copy and then delete the records (within a transaction) whee the FIELD matches the VALUE. But then -again- this is possibly not the task to set before a novice.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[dschomburg]

I can think of many ways to do this. But I think it depends on how often you plan on moving the data. Once a day, week, month? Or every time a Patient is let go?

Is there a field that identifies the current status of a patient?

a little more info is needed.

 

[mac318]

adrift,
This is a simple task using once SQL statements.

1. Insert Into Query.
2. Delete Query.

The first query uses your selection criteria to copy
the selected records to the target database.

The second query deletes the same selected records from
the current database.

It happens so fast that you will need a message box to
inform the user that the task was successful.

mac

 

[adrift]

michael.. I'd already said the tables were IDENTICAL.. I found your response condescending and uncalled for.. I apologize if I've misinterpreted your tone.. thanks for your help

dschomburg, mac.. I figured it would require a couple of SQL statements and I appreciate the confirmation.. a message box is certainly a good idea.. a confirmation of course..

cheers