Rookie Needs Help with Query

[Respite Barb]

I've created a employee training database. One of the tables stores records
of training certifications that expire in 1, 2, or 3 years. In some cases,
an employee will have recertified 2 or 3 times. I'm trying to create a query
that searches for most recent certifications that are about to expire.
Please advise.
Thanks,

 

[Ken Snell \(MVP\)]

Here is a generic SQL statement for the type of query that you want:

SELECT *
FROM TableName
WHERE CertificationDateField =
(SELECT Max(T.CertificationDateField) AS MCDF
FROM TableName AS T
WHERE T.EmployeeIDField = TableName.EmployeeIDField);

 

[John Spencer (MVP)]

Are there multiple types of certifications per employee - First Aid
certification, Computer Tech Certication, etc.?

For each type, what information in the database tells you how long the
certification is good for?

Your first step might be to create a query that returns the employee
identifier, the type of certification, and the MAX (last) date of
certification and calculates the date where recertification is needed using
something like:
DateAdd("m",[CertificationGoodForMonths],Max(CertificationDate)) as DueDate



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Respite Barb]

Ken,
Thanks for this. Greatly appreciated.

 

[Respite Barb]

John, Thanks for your help. Yes, the agency has a centralized training
where 4 out of 10 modules need recertification. The table currently has a
drop-down column identifying which modules need recertification and in how
many years. I like your idea of a query that calculates the date of
expiration. Will give it a shot. Much appreciated.
--
Respite Barb

Are there multiple types of certifications per employee - First Aid
certification, Computer Tech Certication, etc.?

For each type, what information in the database tells you how long the
certification is good for?

Your first step might be to create a query that returns the employee
identifier, the type of certification, and the MAX (last) date of
certification and calculates the date where recertification is needed using
something like:
DateAdd("m",[CertificationGoodForMonths],Max(CertificationDate)) as DueDate



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I've created a employee training database. One of the tables stores records
of training certifications that expire in 1, 2, or 3 years. In some cases,
an employee will have recertified 2 or 3 times. I'm trying to create a query
that searches for most recent certifications that are about to expire.
Please advise.
Thanks,