training database on access-outstanding report looking at wrong da

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a course called AP1 which is sat every 5 years. When I run an
outstanding course report and a member of staff has worked for the company
for 20 years, started in 1985, and sat this course 4 times it still shows as
outstanding in 1990. How do I type in for it to only show course outstanding
if the frequency of 5 years is past the present date but only to look at the
most recent date for this particular course ie 2005 would be the last date
sat therefore it wouldn't show on an outstanding report until 2010. my
formula at the moment is <now () what i need to say is less than now but only
look at the most recent date for this course as there may more than 1.
 
You would need to use a totals query to get the latest date the course was sat.
Since you gave no real detail on your tables, here is a very generic SQL statement.

SELECT EmployeeID, CourseCode, Max(CourseDate) as LastTaken
FROM SomeTable
WHERE CourseCode = "AP1"
GROUP BY Employee, CourseCode
HAVING Max(CourseDate) < DateAdd("yyyy",-5,Date())
 
Back
Top