Move terminated employees from an active table to an inactive table

[ruthcali]

hi,<br>i am creating a Human Resources database. when an employee leaves the company, i thought it would be useful to still keep some info about that person for future use. but, i don't want to keep the terminated people in the same table as the active employees. any idea on how i can move records?<br><br>i am about finished with my database. would anyone be willing to take a look at it to give me any suggestions for improvement? i would really appreciate it!<br><br>thanks,<br>ruth<br>

 

[DougP]

You need a &quot;Yes/No&quot; field type in your Employeee table that is called &quot;Active&quot; so all employees that are still working there, it is checked True or Yes.&nbsp;&nbsp;Then make an &quot;Append&quot; query that has all of the fields including the new one so you can Append the terminiated ones to it.<br><br>Here is a sample SQL<br>---------------------<br>INSERT INTO Terminated<br>SELECT <br>FROM [Emp Backup]<br>WHERE ((([Emp Backup].Active)=-1));<br>---------------------------------------<br>Then you would make a second query that would delete them from the first table.<br>----------------------<br>DELETE [Emp Backup].Active, [Emp Backup].EmployeeID, [Emp Backup].FirstName, [Emp Backup].LastName, [Emp Backup].NewLast, [Emp Backup].DateHired<br>FROM [Emp Backup]<br>WHERE ((([Emp Backup].Active)=0));<br>-------------------------------<br><br>you could then create a macro that would run both of them<br>It will prompt you however before deleting.<br>OK<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.