Copy record to another table

[PJHAction]

I have a database for personnel, the problem is they rotate in and out, so I want to move people who left to a history table.

I have an access table of personnel with two related subtables.

My thought is have a macro delete the records from the sub table then select the record from the tblPersonnel and append it to a tblPersonnel_history, in return delete the record from the tblpersonnel.

Thoughts? If I cannot do this then I will just delete their record!

 

[lespaul]

why not just add a field 'CurrentEmployee' and have it be TRUE or FALSE...then you can set your queries to only return:

[tt]WHERE CurrentEmployee[/tt]

creating a duplicate identical table is just asking for problems in the future...

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[PJHAction]

My concern was having a table slowed down due to too many records, if that is not an issue, then I will just add a field for CurrentEmployee.

Peter

 

[lespaul]

you would have to millions of records before it would start to be a problem and even then if you add an index on that field that would solve slowness. Hopefully you don't have millions of employees!

Leslie

Have you met Hardy Heron?

 

[jrbarnett]

The way we do it is store a contract end date (populated when HR receive notification somebody is leaving). HR staff then input this information to the system.

You can then have a query that retrieves current staff - looks for (contract end date is null) or (contract end date <= date)

It saves dealing with removing old staff, and as lespaul says, storing lots of data in the table won't be a problem unless you have several million employees - by which time you will have needed to migrate to something better than Access.

John