Rookie Needs Help with Query

  • Thread starter Thread starter Respite Barb
  • Start date Start date
R

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,
 
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);
 
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
 
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


John Spencer (MVP) said:
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 said:
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,
Click to expand...
Click to expand...
 
Back
Top