Date Equation on Report

[C F]

All employees have their hire date listed. As well as all of the training
history during their employment

I would like to create a report that only shows the training each employee
has taken for the 12 months after their hire month. For Example, I was
hired in January of 2002. The report should only show the training that I
have take from January 07-Dec 07. Next year I will need to see Jan 08 - Dec
08. I don't need to show all the training from previous years.

Help!! And Thank you!!!

 

[Golfinray]

Try - Between 1/1/2007 and 1/1/2008 or whatever dates you want.

 

[C F]

I need a formula that will locate the hire date of that employee - ie May 5,
2004. I need to pull all training for the current year as well as back to
their last anniversay month. May 06 - Dec 07. I have 40,000 employees that
will be filtered this way.

Any thoughts for how this should be written? I have been able to filter by
month and all of 2007, but can't get it to back up and catch the few months
needed in prior year.

Thank you for any direction you can give us.

 

[John Spencer]

Assumption for 2008 you want the following records
HireMonth Start End
January Jan 07 Dec 07
February Feb 06 Jan 07
March Mar 06 Feb 07
....
Dec Dec 06 Nov 07

Perhaps something like the following will work for you.

SELECT Employees.*, EmpTraining.*
FROM Employees INNER JOIN EmpTraining
ON Employees.EmployeeID = EmpTraining.EmployeeID
WHERE EmpTraining.TrainDate Between
DATESERIAL(Year(Date())-2 - Month(HireDate)=1,Month(HireDate),1) and
DATESERIAL(Year(Date())-1 - Month(HireDate)=1,Month(HireDate),0)




John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County