Can you calculate averages for only a few entries within a group?

[Guest]

I need to calculate the average earnings for employees for their last five
years of employment. Does anyone know how to do this? The table is set up
with multiple rows for each employee, ie:

employee id begin_date end_date earnings
123456 1/1/2004 12/31/2004 $35,000
123456 6/1/2003 12/31/2003 $30,000
123456 1/1/2002 12/31/2002 $27,000
123456 1/1/2001 12/31/2001 $25,000
123456 1/1/2000 12/31/2000 $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

Obviously some employees will have more/less than five years of employment,
and their dates of employment range to as early as the 1970s. I'd like to
just obtain their average annual earnings for each of their last five years
of employment. Any help is greatly appreciated. Thanks!

 

[Guest]

Try this
SELECT EmployeeTable.[employee id], Avg(EmployeeTable.earnings) AS Avgearnings
FROM EmployeeTable
WHERE
(((EmployeeTable.begin_date)>=DateSerial(Year(DateAdd("yyyy",-5,Date())),1,1)))
GROUP BY EmployeeTable.[employee id]

Moving back to the 1/1 5 years from today.
No consideration if the employee started working in the middle of the year
You need to change the name of the table

 

[Guest]

Thanks for the suggestion, but it didn't work.

You wrote that "it moves back to the 1/1 5 years before today," but I'm
looking to get the average earnings for the last five years of employment for
each employee. So the last date an employee could have worked varies, ie, it
could be 12/31/1996, 8/1/1984, or 12/1/2001, etc; the last date won't
necessarily correspond to today's date.

Try this
SELECT EmployeeTable.[employee id], Avg(EmployeeTable.earnings) AS Avgearnings
FROM EmployeeTable
WHERE
(((EmployeeTable.begin_date)>=DateSerial(Year(DateAdd("yyyy",-5,Date())),1,1)))
GROUP BY EmployeeTable.[employee id]

Moving back to the 1/1 5 years from today.
No consideration if the employee started working in the middle of the year
You need to change the name of the table
--
I hope that helped
Good luck

I need to calculate the average earnings for employees for their last five
years of employment. Does anyone know how to do this? The table is set up
with multiple rows for each employee, ie:

employee id begin_date end_date earnings
123456 1/1/2004 12/31/2004 $35,000
123456 6/1/2003 12/31/2003 $30,000
123456 1/1/2002 12/31/2002 $27,000
123456 1/1/2001 12/31/2001 $25,000
123456 1/1/2000 12/31/2000 $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

Obviously some employees will have more/less than five years of employment,
and their dates of employment range to as early as the 1970s. I'd like to
just obtain their average annual earnings for each of their last five years
of employment. Any help is greatly appreciated. Thanks!