wouldn't the calculations be kept within the table
in theory they can be although you say
Enter certain training qualifications, with dates completed. Also simple.
Have Access calculate when the requalification is due to be completed again. There is a drop-dead date for each that is the final day of x months after the qualification was completed initially.
the problem here is that you also say
employees who have multiple training quals, with different expiration dates
I'm reading that as different periods (i.e. not all qualifications have a 6 month expiry date, some will last for 6 months , some for 12, etc) so I would expect the expiration period to be related to the qualification, not the individual - so that value is in a different table - and table calculations can only reference values in the same record
The other issue is that calculated fields cannot be indexed, so if you have large data sets the query will be slow.
I would expect you to have 3 tables
tblEmployees
employeePK
employeeName
tblQualifications
qualificationPK
qualificationName
expiryPeriod
tblQualified
QualifiedPK
employeeFK
qualificationFK
qualifiedDate
and the query would be
Code:
SELECT employeeName, qualificationName
FROM (tblEmployees E INNER JOIN tblQualifications Q ON E.employeePK=Q.employeeFK) INNER JOIN tblQuailified R ON Q.qualificationPK=R.qualificationFK
WHERE Format(DateSerial(year(qualifiedDate), month(qualifiedDate) +expiryPeriod +1, 0),'yyyymm')=[enterDate (yyyymm)]