Auto Delete Record on Date in Field

[SmartestIdiot]

Hello Everyone,

I have a database of personnel. People come and go all the time; however I can't have a personnel record deleted as soon as they leave. They remain on our books until a specified date for each individual. The date is different for each individual. I was hoping to have a field in their record with the date, and have a query or some code delete that record on the date in the field. Is this possible? Thanks.

[June7]

It is, but why bother with delete? Deleting records should be a rare event. Just after you delete, a reason to visit that record will come up - guaranteed. Just filter out the records with a 'terminated' date.

[Demerit]

but why bother with delete? Deleting records should be a rare event. Just after you delete, a reason to visit that record will come up - guaranteed. Just filter out the records with a 'terminated' date.

Actually a Database should have data in it such that future analysis could be done on it. Lets assume we are in the year 2020, you may want to do a historical annual analysis on the number of people who have come and gone since 2013 to 2020. how will you do that if you have been deleting the data. What ever the case, deleting data should be the last thing you should do.

As a matter of fact, there is always enough space to store the data. Hope you see reason in this?

[SmartestIdiot]

Ok. I'm on board with the whole idea of archiving the information to different table or sort, but how do I get it off of the current table and moved automatically based off of a variable date for each record?

[June7]

There is no reason to 'move' records to a different table. Filter queries to exclude the records that have a date in the 'terminated' field.

[SmartestIdiot]

Ok, I know how to query for records with a terminate date. My question is how to I have my queries and reports begin to stop showing the records after the terminate. Keep in mind. Each record will have a different terminate date. So when personnel leave the organization I would have the terminate date entered in possibly a week or even a month out. I'd like them to stay on our books until that date rolls around. At that time they would drop off.

[June7]

Try:

SELECT * FROM table WHERE [terminated] Is Null;

Or

SELECT * FROM table WHERE [terminated] Is Null Or [terminated] > Date();

[SmartestIdiot]

Thanks June7. Looks like that will work. I'll give it a shot and repost.