Historical Table??????

[cornishteeth]

I have a database that holds 100 items and are respectively numbered 1 to 100. These numbers hold the names of individuals with a security clearance. When an individual leaves, say #43, I delete them and reuse the #43 slot. I must retain these individuals information for at least two years. What I want to accomplish is to create an historical table from the ones that are deleted out of my main form. Lets say that I delete #43. I would like to have it somehow transferred over to an historical table so I can use the data for historical purposes. I tried various queries but to no avail. Does anybody have any suggestions or a point in the right direction?

Thanks,

Cornish Teeth

 

[garyd1234]

Create a copy of your main table to use as the historical table.
I'm not sure how you are deleting the record #43. I would put a button on the form and put this code in its click event.
dim db as database
dim rs as recordset
set db=currentdb()
codenumbertodelete = ...put the field on your form corresponding to the record number you want to delete here...
set rs=db.openrecordset("select * from tablename where code = " & codenumbertodelete)
db.execute "insert into historicaltable ( ...field names of table here ..) values (.... values set up from the rs recordset here)"
db.execute "delete from tablename where code = " & codenumbertodelete

 

[cornishteeth]

I have a field that is designated as just a number (1 through 100), then other amplifying data in other fields. I can only retain 100 top secret security clearances at one time. When I delete it, I simply wipe out all the remaining data and simply leave the #43 there. Currently, I retype the data into another form which simply goes to my other "historical" database. Way to time consuming. I thought there was perhaps a way a that a row of information could be removed from a form (database view) amd then somehow put into another historical "holding" table.

 

[SydneyGeek]

Another thing to consider

If you delete #43, say Jim Bowie, and replace him with Cat Ballou, then replace her with Kit Carson, you have no easy way to track histories wherever you put the data.
What happens if Jim Bowie returns as #53?
I'd suggest that you have another unique ID field (an Autonumber would do but maybe the Employee No would be better). Then when you come back to check histories, you can do it with the Employee No -- and you'll see the full record of what security no each employee had as well.