Quit/Terminated Employee Table

[mwabbe]

Have an employee db. eventually people will quit or get fired and they will no longer be needed in the employee directory part, but they can not just be deleted.

I have been thinking of a way to do this and nothing but and append query has come to mind and I have never used one before and not really sure how.



But the plan is to have the Quit/Terminated Employees moved to a seperate table once they become Quit/Terminated.

Thanks

[michaeljohnh]

Two ways to go about this that I can think of:

First:
You will need to have a field in the employee table that retains their status. You would then create a query that checks that field and if it equals "terminated", "resigned" etc then copy to another table through an append query. Once you've comfirmed that they've been copied, run another query to delete those files from the employee table.

As for how to run and append query, when you're on the Design tab there is a button in Query Type that says Append. After choosing button, the fields at the bottom will change and is fairly self explanatory.

Second:
Rather than removing the terminated/resigned, simply use the status field to ignore them when running reports, queries etc. The criteria field in the query is used for that purpose.

[NTC]

MJ's advice is correct. But I will add that in general (for management and legal purposes) you really need to record the 'termination date'. So you need to add that field. When that has a value then of course your queries can leave those people out of any report/form etc by simply using Not Is Null. But it is also useful if you ever need to know the roster of employess in a past quarter by using the <= or something like that.

Hope this helps.

[mwabbe]

i go into query design click append it asks me append to...but i have an employee record that is spread out in multiple tables and even when i try just for one with a criterea it freaks out about using the whole tables with the *

[michaeljohnh]

If I understand correctly, you are saying that portions of an employee record is in mutliple tables. Is there any reason the tables cant be combined into just one employee table for active employees, and then the second table for the previous employees?

Also the table you are appending to should have exactly the same fields as the primary employee table.

[mwabbe]

well it would be one huge table. i have tables now of EmpInfo, Benefits, Emergency cont., Discipline, Training.

Is it suggested they be in one table?

[michaeljohnh]

More complicated than I thought. It sounds like your best bet would be to do what NTC said and just add a separation date field to those tables and then create a query that filters out based on whether that field is null (this would be accomplished through the criteria field, the syntax would be something like Not IsNull). So when you run a report it is based off the query not the table and therefore ignores the ones that have a pupulated separation date field.