automatic updates

I have two sql 2000 server tables one is active and one is terminated (I inherited this db) and I was thinking of having the active table automatically update the terminated table when an employee is terminated. Access is at the front end and in the form of the active table theres a drop down text box that has the employement status(active, terminated, on leave...etc) so when the status turns into terminated i want the acitve table to send those records to the terminated table, ( the data in both tables are not exactly the same). looking into a trigger or stored procedure. This is the first time I've done this so I'm doing some reasearch on how to handle it. Any suggestionsAny suggestions

Yeah,

use a trigger or a stored procedure|||Ok good thank you Brett (reassurance I'm going about it the right way) was thinking a Trigger would be good to update the termination table as soon as the status in the active table is changed to "TERMINATED" it will automatically send the data to that table. Can I show you my code to make sure I'm doing this correctly??

Thanks guys|||Can I show you my code to make sure I'm doing this correctly??Is that anything like "I'll show you mine if..."

NEVERMIND!!!

-PatP|||You know a dirty mind is a terrible thing to waste|||You know a dirty mind is a terrible thing to wasteI'd always heard that a waist was a terrible thing to mind.

-PatP|||You know a dirty mind is a terrible thing to waste

Boy I can see where this is hedading

So Yes you can show me yours...can you send attachments in emails?|||And you should check out our little party we got going on

http://www.dbforums.com/showthread.php?p=3693529#post3693529

The more the merrier...|||I'd always heard that a waist was a terrible thing to mind.

-PatP

Luckily it's just a head shot

http://searchdatabase.techtarget.com/ateExpertBio/0,289623,sid13_cid379843,00.html|||Somebody should submit that to http://www.amihotornot.com/|||You should really quit your day job and do stand up...|||Somebody should submit that to http://www.amihotornot.com/Bleah! It was something like 03:00, about the third day of Tech-Ed. They wanted a picture of me in one of their logo'ed shirts. I never should have agreed!

-PatP|||I used to work with an IT guy who had done stand-up comedy. He was very funny, very similiar to Stephen Colbert on Comedy Central's Daily Show. The problem was that every time you asked him for assistance you had to sit through a minute or two of hillarious but biting sarcasm before he'd solve your problem.|||What did he solve? Excel?

And where did my desiree go?|||I used to work with an IT guy who had done stand-up comedy. He was very funny, very similiar to Stephen Colbert on Comedy Central's Daily Show. The problem was that every time you asked him for assistance you had to sit through a minute or two of hillarious but biting sarcasm before he'd solve your problem.I don't remember that!

-PatP|||I'm still here guys just working on my update trigger, trying to get it to work properly. :)|||Just post it so we can see it...

make sure to add [ code] [ /code] tags...just take out he space|||ok I got two tables one is the EmployeeGL.Table and the other is the Terminations.Table, now both tables have a drop down combo box that has Active or Terminated (termination table has different values in combo box but Terminated is one of them) in it this represents their status. What I want is as soon as the combo box is changed to "Terminated", I want the Termination.Table to updated with that employees information and deleted out of the EmployeeGL.Table. Bare with me cause I'm still new to sql, so I'm still learning how to create stored procedures and triggers.Or perhaps an Append instead of an UPdate Trigger. Sorry still researching and Analyzing. I'll get back with you, gonna research Append (adding to another table rather then updating)|||Perhaps if I append instead of update

INSERT INTO EmployeeGL_Table ( [TM #], FirstName, LastName, [SS #], [Voluntary or Involuntary], TerminationDate )
SELECT TERMINATION.[TM #], TERMINATION.FirstName, TERMINATION.LastName, TERMINATION.SocialSecurityNumber, TERMINATION.VoluntaryorInvoluntary, TERMINATION.TerminationDate
FROM TERMINATION
WHERE (((TERMINATION.Status)="TERMINATED") AND ((TERMINATION.TerminationDate) Between #1/5/2003# And #12/31/2003#));
Perhaps as a stored stored procedure