Automatically moving records from one table to another in the same database. (1 Viewer)

[swmidkiff]

Good Morning,

We have a database that tracks personnel with security clearances. There are 3 tables. One for individuals with ACTIVE clearances, one for individuals who are IN PROCESS and one for individuals that have been DEBRIEFED and are no longer cleared.

What I want to do is set it up so that when an individual who is IN PROCESS is briefed their record would automatically be moved from the IN PROCESS to the ACTIVE table. Likewise, when an individual is DEBRIEFED their record would be moved from the ACTIVE to the DEBRIEFED table.

I'm sure there is a fairly easy way to do this but haven't quite figured it out yet. I want to avoid people monkeying around in the tables at all costs. I've seen the results of this and it's not pretty.

Any suggestions would be greatly appreciated.

 

[plog]

This is better than fairly easy, it's super easy: Normalize your data.

You shouldn't have 3 tables with identical structures, you should have 1 table with an additional field that determines the person's status (i.e. "ACTIVE", "IN PROCESS", "DEBRIEFED"). Now you never have to move data, you simply update its status.

 

[swmidkiff]

plog,

Thanks for the input. Unfortunately this is the way that we received the database AND the way they want it kept which means that I have to find a way to move the data and make it work If I had my druthers I would rebuild it as you stated but it's above my pay grade.

 

[gemma-the-husky]

you need to do this

append the data from table 1 to table 2

then delete the data from table 1

----

I have a few dbs's where I do stuff like this, and i add code to do this

count items in table 2
count items in table 1 to be appended
copy items to be appended to a "saved" table
append items to table 2
count items now in table 2
delete transferred items from table 1

NOW - you need to check that the current item count in table 2 is what you expected (ie the original count plus the new items) - if it is, then there is no problem. if it isn't correct, you have to inform the user and investigate. hence the saved table

it is clearly better not to have to do all this, but sometimes it cannot be helped.

 

[Pat Hartman]

AND the way they want it kept

Who makes these stupid decisions and why do they still have a job?
Sorry, I lost control.