Modification to current code

  • Thread starter Thread starter JudyB
  • Start date Start date
J

JudyB

I have a database that automatically calculates an employee’s service time in
each Job Classification that he/she works in. I have used the following
expression in the control source property of the form:

=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " And [JobTitleName] = Current_Job_Title_Name")

All works well, but now I must modify or make an addition to that code so
that it will also include any service time an employee may work in any job
title that is above his/her current job title in the line of progression
(LOP). For example:

Joe Brown is currently working as a Winder Technician (LOP = 1.1.05) where
he has 52 weeks service. Prior to that position, he had worked as a Dry End
Technician (LOP = 1.1.04) which is a higher job classification and where he
had 25 weeks service. What do I need to do to get the program to
automatically add the 25 weeks served in the higher job classification to the
employee’s current job classification of 52 weeks service to give me a total
time served of 77 weeks? I hope this is clear. Can anyone help? Thanks!
 
Adding machine?

The solution is simple but you may have to make some changes.
In a query or your DSum you need
"AND [LOP] >= CurrentLOP"
That means that there as to be something that will see 1.1.04 as greater
than 1.1.05 and that means...
If you have a relatively small number of decriptions it might be as easy as
adding a numerical "LOPOrder" field and makng the lowest job = 1
Then it would be AND [LOPOrder] >= currentLOPOrder.

If there is more than one LOP, YAF (Yet Another Field) might be needed.
 
Hi Judy,

Are the job classifications structured so that they all start 1.1. and if so
is the ranking order of the final digits 01 highest to 09 lowest? if not what
order are they in and how do they change?

TonyT..
 
Hi Tony,

The first two digits, 1.1, represents the Department. I have several other
Departments with the first two digits of 2.1, 3.1, 4.1. Then the final
digits represent the job classification and are ranked as 01 being the
highest and 09 being the lowest job in the line of progression. I want the
code to add service time where the first two digits are the same. In other
words, if an employee is currently working in the 1.1.04 LOP job and was
demoted from a 1.1.03 LOP job, I want it to add the time worked in the 1.1.03
LOP job to the 1.1.04 LOP job. If the employee was working in the 1.1.04 LOP
job and was demoted from a 2.1.03 LOP job, I do not want the service time
added together as they are in different Departments.

Hope this helps and is clear. Thanks!

Thanks for the help!
--
JudyB


TonyT said:
Hi Judy,

Are the job classifications structured so that they all start 1.1. and if so
is the ranking order of the final digits 01 highest to 09 lowest? if not what
order are they in and how do they change?

TonyT..

JudyB said:
I have a database that automatically calculates an employee’s service time in
each Job Classification that he/she works in. I have used the following
expression in the control source property of the form:

=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " And [JobTitleName] = Current_Job_Title_Name")

All works well, but now I must modify or make an addition to that code so
that it will also include any service time an employee may work in any job
title that is above his/her current job title in the line of progression
(LOP). For example:

Joe Brown is currently working as a Winder Technician (LOP = 1.1.05) where
he has 52 weeks service. Prior to that position, he had worked as a Dry End
Technician (LOP = 1.1.04) which is a higher job classification and where he
had 25 weeks service. What do I need to do to get the program to
automatically add the 25 weeks served in the higher job classification to the
employee’s current job classification of 52 weeks service to give me a total
time served of 77 weeks? I hope this is clear. Can anyone help? Thanks!
Click to expand...
Click to expand...
 
Hi again Judy,

provided the department digits are alway x.x. where x is a single digit AND
the job classification is always 2 digits you could use;

=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " AND Left(LOP, 3) = '" & Left(Current_LOP,3) & "' AND
Right(LOP, 2) <= '" & Right(Current_LOP, 2) & "'")

I haven't tested that, but it should work where LOP is the field in your
underlying table or query and Current_LOP is the Employees current LOP taken
from your form.

This should only return LOP's in the same department with equal or higher
job classifications.

If any of the components x.x.yy in the LOP can vary the number of digits
then have a look at the InStrRev function, you can use that in place of the
Left and Right functions to find the last "." & read the digits before or
after that '.' in the same way.

hth,

TonyT..


JudyB said:
Hi Tony,

The first two digits, 1.1, represents the Department. I have several other
Departments with the first two digits of 2.1, 3.1, 4.1. Then the final
digits represent the job classification and are ranked as 01 being the
highest and 09 being the lowest job in the line of progression. I want the
code to add service time where the first two digits are the same. In other
words, if an employee is currently working in the 1.1.04 LOP job and was
demoted from a 1.1.03 LOP job, I want it to add the time worked in the 1.1.03
LOP job to the 1.1.04 LOP job. If the employee was working in the 1.1.04 LOP
job and was demoted from a 2.1.03 LOP job, I do not want the service time
added together as they are in different Departments.

Hope this helps and is clear. Thanks!

Thanks for the help!
--
JudyB


TonyT said:
Hi Judy,

Are the job classifications structured so that they all start 1.1. and if so
is the ranking order of the final digits 01 highest to 09 lowest? if not what
order are they in and how do they change?

TonyT..

JudyB said:
I have a database that automatically calculates an employee’s service time in
each Job Classification that he/she works in. I have used the following
expression in the control source property of the form:

=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " And [JobTitleName] = Current_Job_Title_Name")

All works well, but now I must modify or make an addition to that code so
that it will also include any service time an employee may work in any job
title that is above his/her current job title in the line of progression
(LOP). For example:

Joe Brown is currently working as a Winder Technician (LOP = 1.1.05) where
he has 52 weeks service. Prior to that position, he had worked as a Dry End
Technician (LOP = 1.1.04) which is a higher job classification and where he
had 25 weeks service. What do I need to do to get the program to
automatically add the 25 weeks served in the higher job classification to the
employee’s current job classification of 52 weeks service to give me a total
time served of 77 weeks? I hope this is clear. Can anyone help? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Hi Tony
I was unsuccessful in getting the following code to work:

,=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " AND Left(LOP, 3) = '" & Left(Current_LOP,3) & "' AND
Right(LOP, 2) <= '" & Right(Current_LOP, 2) & "'")

When I typed the code in the Control Source field and then clicked out
of the field, it was automatically changed back to the original code. Any
suggestions? This is my first database, so I am very green. The last
paragraph of your previous suggestion was very confusing to me. The number
of digits are as you stated. x is always a single digit and y is always two
digits. Did not understand the InStrRev function. Can you provide
additional help? Thank you so much!

--
JudyB


TonyT said:
Hi again Judy,

provided the department digits are alway x.x. where x is a single digit AND
the job classification is always 2 digits you could use;

=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " AND Left(LOP, 3) = '" & Left(Current_LOP,3) & "' AND
Right(LOP, 2) <= '" & Right(Current_LOP, 2) & "'")

I haven't tested that, but it should work where LOP is the field in your
underlying table or query and Current_LOP is the Employees current LOP taken
from your form.

This should only return LOP's in the same department with equal or higher
job classifications.

If any of the components x.x.yy in the LOP can vary the number of digits
then have a look at the InStrRev function, you can use that in place of the
Left and Right functions to find the last "." & read the digits before or
after that '.' in the same way.

hth,

TonyT..


JudyB said:
Hi Tony,

The first two digits, 1.1, represents the Department. I have several other
Departments with the first two digits of 2.1, 3.1, 4.1. Then the final
digits represent the job classification and are ranked as 01 being the
highest and 09 being the lowest job in the line of progression. I want the
code to add service time where the first two digits are the same. In other
words, if an employee is currently working in the 1.1.04 LOP job and was
demoted from a 1.1.03 LOP job, I want it to add the time worked in the 1.1.03
LOP job to the 1.1.04 LOP job. If the employee was working in the 1.1.04 LOP
job and was demoted from a 2.1.03 LOP job, I do not want the service time
added together as they are in different Departments.

Hope this helps and is clear. Thanks!

Thanks for the help!
--
JudyB


TonyT said:
Hi Judy,

Are the job classifications structured so that they all start 1.1. and if so
is the ranking order of the final digits 01 highest to 09 lowest? if not what
order are they in and how do they change?

TonyT..

:

I have a database that automatically calculates an employee’s service time in
each Job Classification that he/she works in. I have used the following
expression in the control source property of the form:

=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " And [JobTitleName] = Current_Job_Title_Name")

All works well, but now I must modify or make an addition to that code so
that it will also include any service time an employee may work in any job
title that is above his/her current job title in the line of progression
(LOP). For example:

Joe Brown is currently working as a Winder Technician (LOP = 1.1.05) where
he has 52 weeks service. Prior to that position, he had worked as a Dry End
Technician (LOP = 1.1.04) which is a higher job classification and where he
had 25 weeks service. What do I need to do to get the program to
automatically add the 25 weeks served in the higher job classification to the
employee’s current job classification of 52 weeks service to give me a total
time served of 77 weeks? I hope this is clear. Can anyone help? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top