Biannual Training Report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Richard - 09 Oct 2006 17:29 GMT

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.

Reply to this Message

Duane Hookom - 09 Oct 2006 17:44 GMT

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.

Signature

Duane Hookom
MS Access MVP

> 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.

Reply to this Message

Richard - 09 Oct 2006 18:23 GMT

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?

> Check the DateAdd() function.
> To archive, create an append query and then delete records from your main
[quoted text clipped - 6 lines]
> > Then I need to archive everything older than 2 years. Any help would be
> > greatly appreciated. Thanks in advance.

Reply to this Message

TedMi - 09 Oct 2006 19:19 GMT

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.

Signature

Ted

Reply to this Message

Richard - 09 Oct 2006 20:11 GMT

Thanks Ted!

> 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
[quoted text clipped - 12 lines]
> anniv date to now. If future, the time window extends from the anniv date 2
> years ago til now.

Reply to this Message