Biannual Training Report

[Guest]

Hi all, I'm stuck trying to figure out how I can pull an employee's training
history for the current and prior year. The year is based on their hire date.
Then I need to archive everything older than 2 years. Any help would be
greatly appreciated. Thanks in advance.

 

[Duane Hookom]

Check the DateAdd() function.
To archive, create an append query and then delete records from your main
table where they exist in the archive table.

 

[Guest]

Thanks for the suggestion, but I have emplyees hired as far back as 1993 who
have taken training through 2006. The report is to be run at least once year
if not more to see what they've taken recently. DateAdd seems to fall short
of taking into account recent activity. Am I missing something?

 

[Guest]

From your initial post, I'm assuming you mean "current year" to be everything
from the employee's last anniversary date til now, and the "previous year" to
be everything between the last anniv. date and the previous anniv. date. Here
is a suggestion for the query to retrieve all training data for the
employee's current and previous years.
CAUTION: Untested Air Code!

SELECT * FROM TrainingHistory
WHERE TrainingDate >= IIF(DatePart("y",[ HireDate)] < DatePart("y", Date()),
DateSerial(Year(Date()-1), Month([HireDate[), Day([HireDate])),
DateSerial(Year(Date()-2), Month([HireDate]), Day([HireDate])))

What this does: The DatePart comparison tests if this year's anniv. date is
in the past or future. If past, the time window extends from last year's
anniv date to now. If future, the time window extends from the anniv date 2
years ago til now.

 

[Guest]

Thanks Ted!