Recurrent training completion date validation

[chaseydog]

I’m running into a issue with a query that I could use some help on. I’m building a database in Access to capture the completion of recurrent training, typically training that needs to be completed on an annual basis. One of the actions I need to perform is to review training records, return the latest record for each employee by training module, and then verify if the completion date is within the last year.

I created a query that returns training records for each employee, converted it to a totals query using Last as a filter on the completion date. So if an employee has tow or more records for the same course I only see the latest one. So far working as intended.



Next I created a field that evaluates the completion date using the following IIf statement

Standing: IIf(([Completion Date]+[Recurrency])>Now(),"Current","Overdue")

The statement works, but it breaks my Last filter, and I’m, back to seeing all of the employees training records, not just the most recent one.

Any ideas on how I should proceed?

[CJ_London]

use max rather than last - last has no meaning without an order. Also Now() includes a time element, may not matter but if completion was a year ago then it won't show up today

would help to see the full sql but I would think you need

Standing: IIf((max([Completion Date])+[Recurrency])>Date(),"Current","Overdue")

[chaseydog]

@ Ajax - Thanks. I believe I have it sorted now, by using 2 queries. The first sorts down to the latest date, the second takes the results of the first and evaluates the training as current or overdue. I did incorporate your suggestions into the final revision.