Please help with calculating averages!

[Guest]

I need to calculate the average earnings for employees for their last five
years of employment. Does anyone know how to do this? Some employees will
have more/less than five years of employment, and their dates of employment
range to as early as the 1970s. I'm NOT seeking averages for the last five
years before today's date--each employee has different termination dates. So
I'm looking to see what are the average earnings for each employees based on
the individual's final five years of employment, whether they were terminated
in 2005 or 1978.
Any help is greatly appreciated. Thanks!

The table is set up with multiple rows for each employee, ie:

employee id begin_date end_date earnings
123456 1/1/2003 12/31/2003 $35,000
123456 6/1/2002 12/31/2002 $30,000
123456 1/1/2001 12/31/2001 $27,000
123456 1/1/2000 12/31/2000 $25,000
123456 1/1/1999 12/31/1999 $22,000
654321 1/1/1992 12/31/1992 $55,000
654321 1/1/1991 12/31/1991 $53,000
654321 1/1/1990 12/31/1990 $50,000
654321 1/1/1989 12/31/1989 $45,000
654321 1/1/1988 12/31/1988 $40,000
654321 1/1/1987 12/31/1987 $38,000

 

[Michel Walsh]

Hi,


A ranking based solution sounds close to the problem.


SELECT a.employeeId, a.begin_date, COUNT(*) As rank, LAST(a.earnings) As
amount
FROM myTable As a INNER JOIN myTable As b
ON a.begin_date <= b.begin_date
GROUP BY a.employeeID, a.begin_date



will give 1 for the most recent begin_date, 2 for the second one, etc.


So, for each employeeID, we only want those with a rank <=1


SELECT a.employeeId, a.begin_date, LAST(a.earnings) As amount
FROM myTable As a INNER JOIN myTable As b
ON a.begin_date <= b.begin_date
GROUP BY a.employeeID, a.begin_date
HAVING COUNT(*) <= 5


What is left is to average the Amounts


SELECT employeeID, AVG(Amount)
FROM (
SELECT a.employeeId, LAST(a.earnings) As amount
FROM myTable As a INNER JOIN myTable As b
ON a.begin_date <= b.begin_date
GROUP BY a.employeeID, a.begin_date
HAVING COUNT(*) <= 5
) AS x

GROUP BY employeeID



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks for the input, this is very helpful.

I tried ranking but something went wrong...the begin_dates aren't being
ranked by 1, 2, 3, etc. For instance, for one employee, his twenty-one
begin_dates are ranked between 23 and 57, with "57" representing his earliest
begin_date in 1966 instead of "1."

 

[Michel Walsh]

Hi,


Indeed, my mistake, in the ON clause, should be:


ON a.employeeID=b.employeeID AND a.begin_date <= b.begin_date



Hoping it may help,
Vanderghast, Access MVP