Moving Obsolete Records to A 2nd Table

[kalyhan]

Hi Everyone.

I have a training database showing all training for my company. I would like
to set up a querey to move all employees who are no longer here from the
main table to another table based on the "TermDate" - if there is a date
here, the employee no longer works here.

Is this possible?

I'm tired of weeding through ex-employees on forms and the main table as
this database grows with new employees. I still want a record of them (in a
new table), just for reference only. On a rare occasion, we re-hire
ex-employees.

Thanks & Best Regards.

 

[Kerry]

Why not just have forms based on a query which just includes Active
employees (TermDate is null)?

 

[Maury Markowitz]

I have a training database showing all training for my company. I would like
to set up a querey to move all employees who are no longer here from the
main table to another table based on the "TermDate" - if there is a date
here, the employee no longer works here.

INSERT INTO tblOldStuff (list of columns)
SELECT (same list of columns)
FROM tblNewStuff
WHERE TermDate IS NOT null

Check the results, and if they worked...

DELETE FROM tblNewStuff WHERE TermDate IS NOT null

Maury

 

[morita insausti kruger]

Hi Everyone.je ne parle pas l'anglais!

I have a training database showing all training for my company. I would
like
to set up a querey to move all employees who are no longer here from the
main table to another table based on the "TermDate" - if there is a date
here, the employee no longer works here.

Is this possible?

I'm tired of weeding through ex-employees on forms and the main table as
this database grows with new employees. I still want a record of them (in
a
new table), just for reference only. On a rare occasion, we re-hire
ex-employees.

Thanks & Best Regards.

 

[Maury Markowitz]

INSERT INTO tblOldStuff (list of columns)
SELECT (same list of columns)

Sorry, I need to be more clear here... the list of columns has to be in the
same order. The list doesn't have to match the database order, however.

One thing you might want to do is see if the records are already in
"tblOldStuff" (which I should have called "tblOutdatedStuff" to be clear).
This is easy too. Let's say there's a unique key on the name column...

INSERT INTO tblOutdatedStuff (list of columns)
SELECT (same list of columns)
FROM tblActiveStuff
WHERE TermDate IS NOT null
AND name NOT IN (select distinct name from tblOutdatedStuff)

If you're running SQL Server as your backend, you can simply type these
queries into Query Analizer. If you're in Access, you can make a Query with
the same code. Just be sure not to turn on the DELETE until you know it's
working!

Maury

 

[kalyhan]

Thanks Kerry and Maury for your quick replies!

I will try your suggestions.

Best Regards,
Karen

 

[Ken Sheridan]

Karen:

I was about to send this a few hours ago when our local electricity
sub-station blew up and put me off-line! Hopefully it will complement the
replies you've already received nevertheless.

Yes, its possible. First copy the structure of your current table to a new
'archive' table, then create a query based ion your existing table with a
criterion on the TermDate column of:

IS NOT NULL

Open the query to check that its returns the correct ex-employees. If so
change it to an 'append' query and append the rows to the new 'archive'
table. Make sure the rows have been appended correctly and then change the
query to a 'dele' query and run it to remove the rows permanently from the
original table.

However, I'd recommend that you don't do the above. Why not simply base you
forms. reports etc on queries where the TermDate column IS NULL? That way
the forms, reports etc will always be up-to-date without you having to
periodically wed out the redundant rows from the table. You can always
access the data for the ex-employees with query WHERE TermDate IS NOT NULL.
If you re-hire an employee you'd simple delete the value in the TermDate
column.

One caveat: if you want to record separate data per term of employment for
any employees who've been hired more than once then the table would have to
be decomposed into Employees and Employments tables with the latter related
to the former on EmployeeID. Any employee re-hired would then have two or
more rows in Employments and one in Employees. Any columns which represent
attributes of each hiring rather than of the employee per se would go in the
Employments table. Your forms and reports would now be based on a query in
which the two tables are joined, again with the criterion WHERE TermDate IS
NULL.

Ken Sheridan
Stafford, England