Looking for a database to track date specific certification

  • Thread starter Thread starter Seymour
  • Start date Start date
S

Seymour

I need to track certification for folks that expire every 2 years. I would
like it to flag me once the individual needs re certification.
 
You need two tables - Folks and Certifications - in a one-to-many relationship.
Select options in the relation to Referential Integerity and Cascade Update.
Use form/subform with Master/Child links set on Folks.ID.
You need to decide how far in advance to alert that the certification will
expire - 1 week, 1 month, etc.
This query to list due re-certification in 3 weeks --
SELECT Folks.Name, DateAdd("yyyy","2",Max([CertDate])) AS CertDueDate
FROM Folks LEFT JOIN Certifications ON Folks.ID = Certifications.ID
GROUP BY Folks.Name, DateAdd("yyyy","2",Max([CertDate]))
HAVING DateAdd("yyyy","2",Max([CertDate])) >= DateAdd("w", -3,Date());

Or you can use an append query to create records of 'Due_Date' as soon as
certification is complete and second query to show all due dates in
descending order. In this case the Certifications table needs two date
fields, one for due and another for certification. The append query would
append for all Folks still Active (Yes/No field). The new due date could be
based on last due or last certification date.

If you have multiple certification with varying cycles then you would need
another table listing them and the re-cert interval.
 
Back
Top