Provide Average # of years of employees in an entire company

[Guest]

Boy, I need someone's help. I was asked to give the President an average #
of years our employees have been employed. We have 671 employees. We have a
field called DateIn which is there hire date. I'm trying to come up with an
expression where it will take today's date and subtract from the DateIn field
(e.g., could be 4.3 years). Then take that calculated field and average the
total #. I don't have a clue how to do this and I really don't want to do
this manually.

Can anybody help me ASAP?

Thanks a bunch!

Susan

 

[John Spencer]

A very good approximation would be

SELECT Avg(DateDiff("d",DateIn,Date())/365.25)
FROM YourTable

In the query grid
-- Select View: Totals from the menu
-- enter the following in a field "cell"
DateDiff("d",[DateIn],Date())/365.25
-- Change Group by to Avg (average)

Run the query

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

 

[John W. Vinson]

Boy, I need someone's help. I was asked to give the President an average #
of years our employees have been employed. We have 671 employees. We have a
field called DateIn which is there hire date. I'm trying to come up with an
expression where it will take today's date and subtract from the DateIn field
(e.g., could be 4.3 years). Then take that calculated field and average the
total #. I don't have a clue how to do this and I really don't want to do
this manually.

Create a Query based on your employee table.

Put in a calculated field:

YearsOnBoard: Round(DateDiff("d", [DateIn], Date()) / 365.25, 1)

to calculate each employee's service length in days and convert to fractional
years. You can round to 1 or 2 decimal places as appropriate.

Chante the query to a Totals query by clicking the Greek Sigma icon (looks
like a sideways M). Include only this field, and use Avg on the Totals row of
the query.

Open the query and you should see one row with one field, showing the desired
average.

John W. Vinson [MVP]

 

[Guest]

Thanks John. You're awesomoe.

I showed the results to my boss and he now wants me to not average anyone
who has been here less than 1 year. Does anyone have any suggestions on how
to do this?

 

[Guest]

Hi John:

Thanks. You're awesome.

I showed the results to my boss and he now wants me to not average anyone
who has been here less than 1 year. Does anyone have any suggestions on how
to do this?

Boy, I need someone's help. I was asked to give the President an average #
of years our employees have been employed. We have 671 employees. We have a
field called DateIn which is there hire date. I'm trying to come up with an
expression where it will take today's date and subtract from the DateIn field
(e.g., could be 4.3 years). Then take that calculated field and average the
total #. I don't have a clue how to do this and I really don't want to do
this manually.

Create a Query based on your employee table.

Put in a calculated field:

YearsOnBoard: Round(DateDiff("d", [DateIn], Date()) / 365.25, 1)

to calculate each employee's service length in days and convert to fractional
years. You can round to 1 or 2 decimal places as appropriate.

Chante the query to a Totals query by clicking the Greek Sigma icon (looks
like a sideways M). Include only this field, and use Avg on the Totals row of
the query.

Open the query and you should see one row with one field, showing the desired
average.

John W. Vinson [MVP]

 

[Michel Walsh]

In the designer, copy the expression in a second column, then, change the
proposed GROUP BY to WHERE, and add the criteria >=1


Vanderghast, Access MVP

Hi John:

Thanks. You're awesome.

I showed the results to my boss and he now wants me to not average anyone
who has been here less than 1 year. Does anyone have any suggestions on
how
to do this?

Boy, I need someone's help. I was asked to give the President an
average #
of years our employees have been employed. We have 671 employees. We
have a
field called DateIn which is there hire date. I'm trying to come up
with an
expression where it will take today's date and subtract from the DateIn
field
(e.g., could be 4.3 years). Then take that calculated field and average
the
total #. I don't have a clue how to do this and I really don't want to
do
this manually.

Create a Query based on your employee table.

Put in a calculated field:

YearsOnBoard: Round(DateDiff("d", [DateIn], Date()) / 365.25, 1)

to calculate each employee's service length in days and convert to
fractional
years. You can round to 1 or 2 decimal places as appropriate.

Chante the query to a Totals query by clicking the Greek Sigma icon
(looks
like a sideways M). Include only this field, and use Avg on the Totals
row of
the query.

Open the query and you should see one row with one field, showing the
desired
average.

John W. Vinson [MVP]

 

[John Spencer]

In the query grid
-- Select View: Totals from the menu
-- enter the following in a field "cell"
DateDiff("d",[DateIn],Date())/365.25
-- Change Group by to Avg (average)
-- Put DateIn in another cell (again)
Change Group by to WHERE
Put > DateAdd("yyyy",-1,Date()) as the criteria


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

 

[Guest]

Thank you!

In the designer, copy the expression in a second column, then, change the
proposed GROUP BY to WHERE, and add the criteria >=1


Vanderghast, Access MVP

Hi John:

Thanks. You're awesome.

I showed the results to my boss and he now wants me to not average anyone
who has been here less than 1 year. Does anyone have any suggestions on
how
to do this?

On Wed, 2 May 2007 08:56:02 -0700, Susan May

Boy, I need someone's help. I was asked to give the President an
average #
of years our employees have been employed. We have 671 employees. We
have a
field called DateIn which is there hire date. I'm trying to come up
with an
expression where it will take today's date and subtract from the DateIn
field
(e.g., could be 4.3 years). Then take that calculated field and average
the
total #. I don't have a clue how to do this and I really don't want to
do
this manually.

Create a Query based on your employee table.

Put in a calculated field:

YearsOnBoard: Round(DateDiff("d", [DateIn], Date()) / 365.25, 1)

to calculate each employee's service length in days and convert to
fractional
years. You can round to 1 or 2 decimal places as appropriate.

Chante the query to a Totals query by clicking the Greek Sigma icon
(looks
like a sideways M). Include only this field, and use Avg on the Totals
row of
the query.

Open the query and you should see one row with one field, showing the
desired
average.

John W. Vinson [MVP]

 

[Guest]

Great. Thanks for all your help. Don't know what I would do sometimes
without this group of great intelligent Access minds.

In the query grid
-- Select View: Totals from the menu
-- enter the following in a field "cell"
DateDiff("d",[DateIn],Date())/365.25
-- Change Group by to Avg (average)
-- Put DateIn in another cell (again)
Change Group by to WHERE
Put > DateAdd("yyyy",-1,Date()) as the criteria


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

Thanks John. You're awesomoe.

I showed the results to my boss and he now wants me to not average anyone
who has been here less than 1 year. Does anyone have any suggestions on
how
to do this?