Purge old records (with a twist)

[NISMOJim]

I would like my database to automatically purge old records from the tables when the database is opened, but not sure if this will be possible, or how to go about doing it. Here is what I have...
A "Permit" table holds personal information such as [LastName], [FirstName], [IssueDate] & [ExpirationDate].
A "Training" table holds information about training sessions like [TrainingDate], [EmployeeName] (in a last name, first name format), [Class], & [Grade].
Some employees may have a permit but not had any training. Conversely, some may have training, but not have a permit.

I need to find a way to compare the [ExpirationDate] and the most recent[TrainingDate]. If both dates were more than 5 years ago, or if only one of the tables has a record of an employee, and that record is longer than 5 years ago, then all records of that employee should be deleted. If someones Permit record is from 20 years ago but they have a record of training from ten years ago, and another from last week, then both tables would be left intact.
I'm easily intimidated by code, but am willing to try if anyone has any ideas and alot of patience. Thanks, as always, for any & all help.

[June7]

Why do you feel compelled to delete data? There is rarely a real need to resort to this. Any queries and/or code designed to accomplish this could just as well be used to simply filter records.

[NISMOJim]

Part of the reason is database size. Records are already around 8,000, which I guess is no big deal, but may slow down some searches. The main reason is company policy on records retention. Records are normally purged manually once a year, but it would be nice if it were just done automatically when opened. No training or annual reminders would be required. I assume this will not be an easy thing to make happen.
Thanks again

[June7]

8,000 records is a drop in a pool and should not significantly slow down searches. I can understand a policy for purging paper records because of space paper occupies, but digital storage is another story. We also have guidelines for records retention and regularly dispose of paper after scanning to PDF. The database records go back 20+ years and is up around 100,000 records. Even this is not a terribly large file. The real concern with Access is file size, not number of records. Access limit is 2gb.

Yes, this could be automated. I use only VBA so would have procedure in the Open event of a form that is set as default to open when project is opened.

[NISMOJim]

I understand putting VBA in the Open event, but I'm not sure where to go from there. I thought about a query with the name & expiration date from the permit table along with the max training date from the training table, but I don't think it would delete the records, much less all of the other records of the same person in the training table. This is keeping in mind that referential integrity shouldn't come into play since both tables may actually have orphan records (I think this is the correct terminology).
Any further guidance?
Thanks

[June7]

I don't know your data so can't give specifics.

You might be able to perform a nested DELETE SELECT. Check out this tutorial http://www.techonthenet.com/sql/delete.php

Or it may be more complicated requiring a series of queries to arrive at the set of records that should be deleted or may even need to program coded recordsets and loop through the recordsets to examine each record.

[NISMOJim]

Sorry for the delay in response, but I haven't had much time to work on this project lately. Here is what I have come up with so far...

I created a query called "qryPermitName". This combines the last & first names (in a field called EName) from the Permits table, along with the expiration date (ExpDate).
I created another query called "qryFindMaxPermitDate" based on the above query to find the most recent permit expiration date.
I also created a query called "qryFindMaxTrngDate" based on the Training table with the fields EName and TrngDate to get the most recent training date of the employees.

Now comes the hard part that I can't figure how to continue.

If the same employee has a max date in "qryFindMaxPermitDate" that is <(Now)-1826 and a max date in "qryFindMaxTrngDate" that is <(Now)-1826 then delete that employee's record from "qryPermitName", and all of that employee's records from the Training table (right now, "qryPermitName" is read only, and won't allow deleting records).
If an employee has a max date in "qryFindMaxPermitDate" that is <(Now)-1826 but has no records in "qryFindMaxTrngDate" then delete that employee's record from "qryPermitName".
If an employee has a max date in "qryFindMaxTrngDate" that is <(Now)-1826 but has no records in "qryFindMaxPermitDate" then delete all of that employee's records from the Training table.

I believe this can be done somehow in VBA and put in the On Open property of the Main Menu form that opens with the database, but I could sure use some help in making it happen.

Thank you.

[June7]

The following might work. I would need data to test to be sure.

I presume each employee has unique ID? Something other than first & last names? Each of these queries should return one record for each employee, right? Include the unique ID in each query. Join these 3 queries on the unique ID to the table of employees. Hopefully this results in a recordset that has one record for each and every employee still in the employees table, each record showing values or nulls in the fields from each of the 3 queries. Apply the criteria you describe to further filter the records. Now run DELETE SELECT queries for each of the tables you want to purge.

[NISMOJim]

This might present a problem. Each employee in the Permits table has a unique number. Some people may have more than one Permit Number if they worked for different companies on their different visits. However, the Training table assigns an autonumber for each training record. Some of the people in the Training table aren't in the Permit table (like office workers that don't drive, but do take some training classes), and most people in the Training table have several records of the different courses they have taken. That's the reason I went through the trouble of the "EName" field to be a common denominator for both tables.
As things are now, the "qryFindMaxPermitDate" & "qryFindMaxTrngDate" each show only one record per person. I was hoping for something in VBA that could check the max dates in these queries in order to delete the records in the tables. If a restructuring of the tables/forms is required to make this happen, it may be easier for me to manually delete the records on occasion.
Thanks for all your help.

[rpeare]

pardon me for jumping on this thread late.

There is nothing about regular database maintenance that can be done more easily than with code. Manually deleting records is a very bad idea and I would side with June that it's likely a mistake to do this. In any table where I may need to ignore records I have a 'void' yes/no field and I update that value to -1 (yes) if that record is no longer valid or to be reported on anything, then in all my queries I filter by <>-1 for that 'void' field. Once you delete your data it is gone forever and if you do need to go back to look at old training records you're screwed. I'll also say that the policy in my office dictates that we destroy records that are older than 30 days to 7 years depending on the context of the data *and* there are some we are required to keep permanently. But we maintain data in our databases far longer than that because we need a history and it does not affect physical storage or exposure of theft/destruction.

The way you have gone about finding the max value for each of your tables you're interested is the right step.

What I would do next is to add your employee table, then link it to each one of these tables so that you have a full record of every employee with a maxiumum date for each of your two tables (if they exist).

Example:

tblEmployees
EmpID EmpName
1     Mickey
2     Donald
3     Minnie

tblTraining
TrainID EmpID TrainingDate
1       1     1/1/2001
2       2     1/1/2011

tblPermit
PermID EmpID ExpirationDate
1      2     1/1/2002
2      3     1/1/2007

qryFindMaxTrngDate would return
EmpID MaxTrDate
1     1/1/2001
2     1/1/2011

qryFindMaxPermitDate would return
EmpID MaxPeDate
2     1/1/2002
3     1/1/2007

And the query I'm suggestiong would return

EmpID MaxTrDate MaxPeDate
1     1/1/2001
2     1/1/2011  1/1/2002
3               1/1/2007

From there it's pretty simple to cycle through the records and compare the dates side by. If you found that either/both of the dates were more than five years old you could then issue a mass update query command that would update all records in each of your tables for the EMPID of the person being examined.

[June7]

Great rpeare! The last query is what I was aiming at. Only I further suggested including filter criteria to that query to identify employee IDs that are not current. Create another field with a nested IIf expression for the record age conditions that will return True or False. True meaning record is not active and set filter criteria for that field to =True. Then run a DELETE SELECT or go with the Active/Inactive field and run update on that field. Just one more query, no VBA recordsets to cycle.