tracking employee evaluations

[Donna]

Hi,

I need to project who needs their evaluation done by month. There are 2
classifications, full-time and part-time. Full-time is evaluated 1 year
after hire and every three years after based on their last evaluation date.
Part-time is evaluated 2 months, 6 months after hire and every year after
that based on the last evaluation date, not the hire date.

I can't quite figure out 2 things. 1. how to calculate first on the hire
date, but then use the last evaluation date. And 2. How get this all in one
list that shows who needs their evaluation done by month.
Thanks

 

[Allen Browne]

Presumably you have tables something like this:

Employee table:
EmployeeID AutoNumber
Surname Text
FirstName Text
HireDate Date/Time
StatusID Text ("full" or "part")

Evaluation table:
EvaluationID AutoNumber
EmployeeID Foreign key to Employee.EmployeeID
EvalDate Date/Time

If so, you can get the LastEvalDate in a Totals query, and then use that in
a calculated field. It will be something like this:

SELECT Employee.EmployeeID,
Employee.HireDate,
Employee.StatusID,
Max(Evaluation.EvalDate) AS LastEvalDate,
CVDate(IIf([StatusID]="full",
IIf([LastEvalDate] Is Null,
DateAdd("yyyy",1,[HireDate]),
DateAdd("yyyy",3,[LastEvalDate])),
IIf([LastEvalDate] Is Null,
DateAdd("m",2,[HireDate]),
DateAdd("m",6,[LastEvalDate])))) AS NextDue
FROM Employee LEFT JOIN Evaluation
ON Employee.EmployeeID = Evaluation.EmployeeID
GROUP BY Employee.EmployeeID, Employee.HireDate, Employee.StatusID;

If you actually want to sort or filter on the date field, it might be better
to do that as 2 queries. The first gives you the LastEvalDate, and the 2nd
lets you use that more powerfully in the calculated field.

BTW, these queries are read-only.

 

[Donna]

Awesome! Where do you go to learn all this complicated formulas?

Presumably you have tables something like this:

Employee table:
EmployeeID AutoNumber
Surname Text
FirstName Text
HireDate Date/Time
StatusID Text ("full" or "part")

Evaluation table:
EvaluationID AutoNumber
EmployeeID Foreign key to Employee.EmployeeID
EvalDate Date/Time

If so, you can get the LastEvalDate in a Totals query, and then use that in
a calculated field. It will be something like this:

SELECT Employee.EmployeeID,
Employee.HireDate,
Employee.StatusID,
Max(Evaluation.EvalDate) AS LastEvalDate,
CVDate(IIf([StatusID]="full",
IIf([LastEvalDate] Is Null,
DateAdd("yyyy",1,[HireDate]),
DateAdd("yyyy",3,[LastEvalDate])),
IIf([LastEvalDate] Is Null,
DateAdd("m",2,[HireDate]),
DateAdd("m",6,[LastEvalDate])))) AS NextDue
FROM Employee LEFT JOIN Evaluation
ON Employee.EmployeeID = Evaluation.EmployeeID
GROUP BY Employee.EmployeeID, Employee.HireDate, Employee.StatusID;

If you actually want to sort or filter on the date field, it might be better
to do that as 2 queries. The first gives you the LastEvalDate, and the 2nd
lets you use that more powerfully in the calculated field.

BTW, these queries are read-only.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I need to project who needs their evaluation done by month. There are 2
classifications, full-time and part-time. Full-time is evaluated 1 year
after hire and every three years after based on their last evaluation
date.
Part-time is evaluated 2 months, 6 months after hire and every year after
that based on the last evaluation date, not the hire date.

I can't quite figure out 2 things. 1. how to calculate first on the hire
date, but then use the last evaluation date. And 2. How get this all in
one
list that shows who needs their evaluation done by month.
Thanks

 

[Allen Browne]

Actually, it's not that hard, Donna.

That calculated field really consists of only 2 things:
- IIf()
- DateAdd()
If you look up help on those 2 things, it might make sense.

From there, it's a matter of experience, i.e. if you look up help and figure
out how it works now, you will be able to create this stuff later.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Awesome! Where do you go to learn all this complicated formulas?

Presumably you have tables something like this:

Employee table:
EmployeeID AutoNumber
Surname Text
FirstName Text
HireDate Date/Time
StatusID Text ("full" or "part")

Evaluation table:
EvaluationID AutoNumber
EmployeeID Foreign key to Employee.EmployeeID
EvalDate Date/Time

If so, you can get the LastEvalDate in a Totals query, and then use that
in
a calculated field. It will be something like this:

SELECT Employee.EmployeeID,
Employee.HireDate,
Employee.StatusID,
Max(Evaluation.EvalDate) AS LastEvalDate,
CVDate(IIf([StatusID]="full",
IIf([LastEvalDate] Is Null,
DateAdd("yyyy",1,[HireDate]),
DateAdd("yyyy",3,[LastEvalDate])),
IIf([LastEvalDate] Is Null,
DateAdd("m",2,[HireDate]),
DateAdd("m",6,[LastEvalDate])))) AS NextDue
FROM Employee LEFT JOIN Evaluation
ON Employee.EmployeeID = Evaluation.EmployeeID
GROUP BY Employee.EmployeeID, Employee.HireDate, Employee.StatusID;

If you actually want to sort or filter on the date field, it might be
better
to do that as 2 queries. The first gives you the LastEvalDate, and the
2nd
lets you use that more powerfully in the calculated field.

BTW, these queries are read-only.

Hi,

I need to project who needs their evaluation done by month. There are
2
classifications, full-time and part-time. Full-time is evaluated 1
year
after hire and every three years after based on their last evaluation
date.
Part-time is evaluated 2 months, 6 months after hire and every year
after
that based on the last evaluation date, not the hire date.

I can't quite figure out 2 things. 1. how to calculate first on the
hire
date, but then use the last evaluation date. And 2. How get this all
in
one
list that shows who needs their evaluation done by month.
Thanks

 

[Donna]

I get each formula in and of itself, but it's the combining that my tiny
little mind just can't think of by myself! For instance, I have now 3
reports that generate employee evals for 2 months, 6months, and 1 year
(thanks to you!). However, I can't quite figure out how to get them on one
report and grouped per month due...

Thanks for all your help! These 3 reports are way better (yet not perfect)
are better than what we were using before

Actually, it's not that hard, Donna.

That calculated field really consists of only 2 things:
- IIf()
- DateAdd()
If you look up help on those 2 things, it might make sense.

From there, it's a matter of experience, i.e. if you look up help and figure
out how it works now, you will be able to create this stuff later.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Awesome! Where do you go to learn all this complicated formulas?

Presumably you have tables something like this:

Employee table:
EmployeeID AutoNumber
Surname Text
FirstName Text
HireDate Date/Time
StatusID Text ("full" or "part")

Evaluation table:
EvaluationID AutoNumber
EmployeeID Foreign key to Employee.EmployeeID
EvalDate Date/Time

If so, you can get the LastEvalDate in a Totals query, and then use that
in
a calculated field. It will be something like this:

SELECT Employee.EmployeeID,
Employee.HireDate,
Employee.StatusID,
Max(Evaluation.EvalDate) AS LastEvalDate,
CVDate(IIf([StatusID]="full",
IIf([LastEvalDate] Is Null,
DateAdd("yyyy",1,[HireDate]),
DateAdd("yyyy",3,[LastEvalDate])),
IIf([LastEvalDate] Is Null,
DateAdd("m",2,[HireDate]),
DateAdd("m",6,[LastEvalDate])))) AS NextDue
FROM Employee LEFT JOIN Evaluation
ON Employee.EmployeeID = Evaluation.EmployeeID
GROUP BY Employee.EmployeeID, Employee.HireDate, Employee.StatusID;

If you actually want to sort or filter on the date field, it might be
better
to do that as 2 queries. The first gives you the LastEvalDate, and the
2nd
lets you use that more powerfully in the calculated field.

BTW, these queries are read-only.

Hi,

I need to project who needs their evaluation done by month. There are
2
classifications, full-time and part-time. Full-time is evaluated 1
year
after hire and every three years after based on their last evaluation
date.
Part-time is evaluated 2 months, 6 months after hire and every year
after
that based on the last evaluation date, not the hire date.

I can't quite figure out 2 things. 1. how to calculate first on the
hire
date, but then use the last evaluation date. And 2. How get this all
in
one
list that shows who needs their evaluation done by month.
Thanks

 

[Allen Browne]

Donna, I don't understand this question, or how it relates to the parameter
problem.

The grouping-per-month would be done through the report's Grouping And
Sorting dialog.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I get each formula in and of itself, but it's the combining that my tiny
little mind just can't think of by myself! For instance, I have now 3
reports that generate employee evals for 2 months, 6months, and 1 year
(thanks to you!). However, I can't quite figure out how to get them on
one
report and grouped per month due...

Thanks for all your help! These 3 reports are way better (yet not
perfect)
are better than what we were using before

Actually, it's not that hard, Donna.

That calculated field really consists of only 2 things:
- IIf()
- DateAdd()
If you look up help on those 2 things, it might make sense.

From there, it's a matter of experience, i.e. if you look up help and
figure
out how it works now, you will be able to create this stuff later.

Awesome! Where do you go to learn all this complicated formulas?

:

Presumably you have tables something like this:

Employee table:
EmployeeID AutoNumber
Surname Text
FirstName Text
HireDate Date/Time
StatusID Text ("full" or "part")

Evaluation table:
EvaluationID AutoNumber
EmployeeID Foreign key to Employee.EmployeeID
EvalDate Date/Time

If so, you can get the LastEvalDate in a Totals query, and then use
that
in
a calculated field. It will be something like this:

SELECT Employee.EmployeeID,
Employee.HireDate,
Employee.StatusID,
Max(Evaluation.EvalDate) AS LastEvalDate,
CVDate(IIf([StatusID]="full",
IIf([LastEvalDate] Is Null,
DateAdd("yyyy",1,[HireDate]),
DateAdd("yyyy",3,[LastEvalDate])),
IIf([LastEvalDate] Is Null,
DateAdd("m",2,[HireDate]),
DateAdd("m",6,[LastEvalDate])))) AS NextDue
FROM Employee LEFT JOIN Evaluation
ON Employee.EmployeeID = Evaluation.EmployeeID
GROUP BY Employee.EmployeeID, Employee.HireDate, Employee.StatusID;

If you actually want to sort or filter on the date field, it might be
better
to do that as 2 queries. The first gives you the LastEvalDate, and the
2nd
lets you use that more powerfully in the calculated field.

BTW, these queries are read-only.

Hi,

I need to project who needs their evaluation done by month. There
are
2
classifications, full-time and part-time. Full-time is evaluated 1
year
after hire and every three years after based on their last
evaluation
date.
Part-time is evaluated 2 months, 6 months after hire and every year
after
that based on the last evaluation date, not the hire date.

I can't quite figure out 2 things. 1. how to calculate first on
the
hire
date, but then use the last evaluation date. And 2. How get this
all
in
one
list that shows who needs their evaluation done by month.
Thanks

 

[null George Petch International]

Hi,
Can anyone help me on this:
I want to send advise emails to a group of people, but no one is allowed to
see the email address of the other because I am in a financial business...
I tried cc, bcc, looked for hours to find a solution...
Can anyone help me???
Tine from Belgium, Ostend

Awesome! Where do you go to learn all this complicated formulas?

Presumably you have tables something like this:

Employee table:
EmployeeID AutoNumber
Surname Text
FirstName Text
HireDate Date/Time
StatusID Text ("full" or "part")

Evaluation table:
EvaluationID AutoNumber
EmployeeID Foreign key to Employee.EmployeeID
EvalDate Date/Time

If so, you can get the LastEvalDate in a Totals query, and then use that in
a calculated field. It will be something like this:

SELECT Employee.EmployeeID,
Employee.HireDate,
Employee.StatusID,
Max(Evaluation.EvalDate) AS LastEvalDate,
CVDate(IIf([StatusID]="full",
IIf([LastEvalDate] Is Null,
DateAdd("yyyy",1,[HireDate]),
DateAdd("yyyy",3,[LastEvalDate])),
IIf([LastEvalDate] Is Null,
DateAdd("m",2,[HireDate]),
DateAdd("m",6,[LastEvalDate])))) AS NextDue
FROM Employee LEFT JOIN Evaluation
ON Employee.EmployeeID = Evaluation.EmployeeID
GROUP BY Employee.EmployeeID, Employee.HireDate, Employee.StatusID;

If you actually want to sort or filter on the date field, it might be better
to do that as 2 queries. The first gives you the LastEvalDate, and the 2nd
lets you use that more powerfully in the calculated field.

BTW, these queries are read-only.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I need to project who needs their evaluation done by month. There are 2
classifications, full-time and part-time. Full-time is evaluated 1 year
after hire and every three years after based on their last evaluation
date.
Part-time is evaluated 2 months, 6 months after hire and every year after
that based on the last evaluation date, not the hire date.

I can't quite figure out 2 things. 1. how to calculate first on the hire
date, but then use the last evaluation date. And 2. How get this all in
one
list that shows who needs their evaluation done by month.
Thanks