Calculating employee labor costs

[Jan Il]

Hi all - Access 2000 - WinME

I am in need of a query to calculate employee labor costs based upon the
employee job classification or employee ID. I have a table that lists the
employee ID number and their job classification. This is related to a table
that lists the hourly rate based upon the job classification. I have a data
entry form to enter repair activities, but, we need to be able to track the
man-hours to complete the repairs, and the cost of labor based upon either
the employees job classification, or employee ID, which is related to their
job classification hourly wage amount.

I am not sure which of these would be the best to use to get the
calculations I need. What I am thinking is using the employee ID number,
which is tied to the job classification hourly wage amount. But, since
their job classification can change, with their training and elevation from
Apprentice to Master Electrician...perhaps the job classification should be
used instead of the employee ID number. However....their employee number
will never change, but, their job classification can....?<>?

I am just not sure which way I should set up the query to select and
calculate this information. I will need a control on the entry form to make
the selection for the manner of how the cost is calculated, which I am
thinking a drop down or list box with all the employee ID's or the job
classifications....whichever way I need to go.

I would truly appreciate some input or suggestions on this, or perhaps a KB
or Help file that might clarify this issue. Hope I have explained it
somewhat clearly, but, if not, I'll run a search of the the gray-matter
between the ears for a hopefully better explanation. ;-))

Best regards,
Jan

 

[Jeff Boyce]

Jan

What isn't clear from your description is whether your database stores an
employee's job classification AT TIME OF LABOR.

For example, John Smith could have worked for the last 30 years, and served
in every job classification in that time. To calculate cost of labor on
Job#999, you need to know who worked on it (John), how long (3.5 hours), and
what John's classification was at that point in time.

Do you have a table that lists employees and their begin & end dates for
each job classification they've held? Does your labor table list the job
class (either JobClassID-preferable, or the text of the job class) in effect
at time of work?

How you build the query depends on how/what data you have.

Good luck

Jeff Boyce
<Access MVP>

 

[Jan Il]

Hi Jeff!

Jan

What isn't clear from your description is whether your database stores an
employee's job classification AT TIME OF LABOR.

For example, John Smith could have worked for the last 30 years, and served
in every job classification in that time. To calculate cost of labor on
Job#999, you need to know who worked on it (John), how long (3.5 hours), and
what John's classification was at that point in time.

Do you have a table that lists employees and their begin & end dates for
each job classification they've held? Does your labor table list the job
class (either JobClassID-preferable, or the text of the job class) in effect
at time of work?

How you build the query depends on how/what data you have.

Good luck

Jeff Boyce
<Access MVP>

Yes, in the tblEmlpoyee there are the following fields to allow for job
classification changes:
tblEmployee
EmpID (the employee's company ID number)
JobClassStep (represents the job classification level, i.e., Apprentice,
Lineman, Electromechanic)
JobClassDate (date the emp. was promoted to their current classification
level)
JobChangeDate (date they were promoted, this to maintain the previous
classification information)
EmpInactiveDate (last date of employment)
..
In the table where the repairs are recorded, there are fields to record the
number of man-hours required to make the repair, how many employees worked
on the job and their names:
ManHrs
NoEmp
EmpID

There's also a table for the pay rate for the different job classifications.
There are 3 pay rate increases for each classification level, based on the
number of hours worked in that classification level. Thus, when an employee
comes in, they start as Apprentice Assistant Lineman which requires so many
hours of On-The-Job training as well as classroom training (Community
College). The normal time necessary to complete the required training is
usually 2 years. During that time, there will be 3 increases in pay for
that classification. The next class level is Lineman, and there are 3 pay
increases for that level, with the last level being Electromechanic, and
there are 3 increases for that level. So to track the changes in pay rates
the table has:
tblPayRate
PayRateID
AssocJobClass (the job class)
PayRate (the rate of pay for the job class)
StepRate (the new rate of pay for the next job class level)

I am not sure if I may need to add either more fields to the existing
tables, or a another table to address the addition of the cost of labor
function. What I was thinking was to tie the employee ID to their pay rate
in order to calculate and record the labor costs per employee, as 1) their
name could change if they married, divorced, changed their name legally,
etc.; 2) their job class and pay rates can change with promotions; 3) their
employee ID number will never change. Thus, it would seem to be the most
likely choice, and would more efficiently reflect the employee's current job
class and pay rate at the time each job is done. But, I'm just not sure on
this.

After all this, I hope it makes some sense somewhere ;-))

Thank you for your time to assist with this issue, I really do appreciate
it.

Best regards,
Jan

 

[Jeff Boyce]

Jan

I still didn't notice, from your description, where the previous job
classification is stored. It sounds like you only have a "current" job
class.

One way to do this is to create a new Employee X JobClass record, with begin
(and end) date, something like:

trelEmployeeJobClass
EmployeeJobClassID (?autonumber)
EmployeeID (foreign key, back to tblEmployee)
JobClassID (foreign key, back to tblJobClass)
BeginDate
EndDate (left null until the jobclass ends)

To record who did work, I'd use the EmployeeJobClassID. By joining to the
above table, I can always see who (by further joining back to tblEmployee)
and jobclass/rate (by joining back to tblJobClass).

If you happen to be working in SQL-Server, you could create a view with all
these joins already in place. Ditto for work in Access, with a query.

To find "current" employees, just look at the EmployeeJobClass table, for
folks without EndDates (let's just hope you never get employees without
jobclass!).

Good luck!

Jeff Boyce
<Access MVP>

 

[Jan Il]

Hi Jeff - Comments below

Jan

I still didn't notice, from your description, where the previous job
classification is stored. It sounds like you only have a "current" job
class.

I guess that is sorta where I was leaning with the JobChangeDate field,
however, this does not record what their actual job classification was
before it was changed, it only addresses the date of change in
classification. I see your point with the need to maintain the history of
actual job classification for each Maintainer as they proceed through the
various levels (Steps) for each classification. It is a very good point,
thank you for pointing me in that direction.

One way to do this is to create a new Employee X JobClass record, with begin
(and end) date, something like:

trelEmployeeJobClass
EmployeeJobClassID (?autonumber)
EmployeeID (foreign key, back to tblEmployee)
JobClassID (foreign key, back to tblJobClass)
BeginDate
EndDate (left null until the jobclass ends)

To record who did work, I'd use the EmployeeJobClassID. By joining to the
above table, I can always see who (by further joining back to tblEmployee)
and jobclass/rate (by joining back to tblJobClass).

'k......this makes sense....but...and sorry to be so dense here...what would
I use for the EmployeeJobClassID? I'm afraid you lost me on that one. Would
that be the same as the employee company ID number? Yes? No? We currently
don't have job class numbers, but, I could set up numbers for them for our
department classifications, as our employees are the only ones we track for
recording purposes, so creating number identifications for each
classification and the various levels of each, would not be a problem if it
would be best to have such a method for this type of process.

If you happen to be working in SQL-Server, you could create a view with all
these joins already in place. Ditto for work in Access, with a query.

No, I don't work in SQL-Server, just Access, so I guess I have to set up a
query.

To find "current" employees, just look at the EmployeeJobClass table, for
folks without EndDates (let's just hope you never get employees without
jobclass!).

(let's just hope you never get employees without jobclass!)<

They don't dare clock in without it......<bg>


Thanks Jeff, I truly appreciate all your time and help. I'll get busy
setting up the table as you instructed.

Jan

 

[Jeff Boyce]

Jan

I offered the notion of a new table, with EmployeeJobClassID, to get around
the problem of having no history. If you don't NEED a history, you don't
need this type of structure.

If you need to be able to look back over a week or month or year (I don't
know how often job classes change), you DO need a history.

The EmployeeJobClassID would go in the "Job" table, as a link back, via
additional joins, to determine the person and job class. Notice that you
could have 27 EmployeeJobClassIDs, all pointing to the same EmployeeID (see
earlier structure)--I guess that person gets promoted a lot! On any one
Job, you'd only have the one (via the links).

Now, a further wrinkle ... if you ALSO need to show more than one person on
a job (or the same person working in more than one JobClass - got promoted
in the middle of the job), you need another table -- something like the:

trelJobEmployeeJobClass
JobEmployeeJobClassID (?autonumber)
JobID (foreign key, points at a table of Jobs)
EmployeeJobClassID (foreign key, points at the EmployeeJobClass
table, which you would join as previously discussed)
EmployeeHours (how many hours did THIS employee {actually,
EmployeeJobClass} work on THIS Job?)

To find the total hours on a job, query this last table by JobID.

To find the total hours of a given Employee, work backwards through the
EmployeeJobClass table to all JobEmployeeJobClass entries, summing the
EmployeeHours.

(This design hinges on you only entering the total hours worked. If you
need to track each work session on the job, it starts getting complex!)

Have I totally confused you now?!

Good luck

Jeff Boyce
<Access MVP

 

[Jan Il]

Hi Jeff!

Jan

I offered the notion of a new table, with EmployeeJobClassID, to get around
the problem of having no history. If you don't NEED a history, you don't
need this type of structure.

of what you have suggested, and that is it is a very good point that I had
not considered before.

If you need to be able to look back over a week or month or year (I don't
know how often job classes change), you DO need a history.

Well...I agreed with your point regarding this issue as a very good aspect,
due to...'k.....say we have a failure of a crossing gate to activate
properly,
due to either equipment failure, or our power supplier (SDG&E) having a
power failure in the area that causes the equipment to fail to function
properly. Now, let's assume that during this time, when the crossing
equipment is not working properly, the gates do not some down as they should
to provide proper traffic protection, and there is an accident between a
train and a vehicle, resulting in damage or injuries on either side, then
the Maintainer who was responsible for inspecting and or repairing the
crossing equipment would come into play with possible resultant litigation.
Keeping this in mind, lets say that it goes to court, and the qualifications
of the Maintainer who made the inspection or repairs at that time came
into question. Some litigations can go back as much as a year or more,
during which time the Maintainer's qualifications and promotion to the
next classification level could change. But, the courts are not interested
in
what the classification of the employee is at the time of the inquiry,
but, at the time the inspection or repair was made. This could perhaps
become a very important factor.

Have I made some sense with this? I only bring this up to establish the
validity >

The EmployeeJobClassID would go in the "Job" table, as a link back, via
additional joins, to determine the person and job class. Notice that you
could have 27 EmployeeJobClassIDs, all pointing to the same EmployeeID (see
earlier structure)--I guess that person gets promoted a lot! On any one
Job, you'd only have the one (via the links).

Now, a further wrinkle ... if you ALSO need to show more than one person on
a job (or the same person working in more than one JobClass - got promoted
in the middle of the job), you need another table -- something like the:

trelJobEmployeeJobClass
JobEmployeeJobClassID (?autonumber)
JobID (foreign key, points at a table of Jobs)
EmployeeJobClassID (foreign key, points at the EmployeeJobClass
table, which you would join as previously discussed)
EmployeeHours (how many hours did THIS employee {actually,
EmployeeJobClass} work on THIS Job?)

If my gray-matter is compiling this correctly, then this would provide for
the fact that there can be more than one Maintainer involved with the
repair, and they could be of various levels of job classifications, with
various pay rates. This is in fact exactly what can happen. Let's say, we
have a major situation like a train's pantograph (the part of the train that
makes contact with the overhead electrical wiring that powers the
train..trolley) runs into a sag area in the line caused from extreme heat,
which can happen in certain sections of our system. If this happens, it can
tear down several hundred feet of catenary power line. At which time, a
system wide *911* goes out to all Wayside Maintainers, no matter what their
job
classification, to report to the emergency site. There could be Maintainers
from day one Assistant Lineman, to Electormechanics (same as top Journeyman
electricians). There could also be off-duty employees called in to
assist, depending on the severity of the situation and who is available.
So, yes, job classification can play a very important factor in calculating
the cost of repairs. That is why I stated that your suggestion on this was a
very good point..promoting both brain matter and hair to another shade of
gray

To find the total hours on a job, query this last table by JobID.

To find the total hours of a given Employee, work backwards through the
EmployeeJobClass table to all JobEmployeeJobClass entries, summing the
EmployeeHours.

(This design hinges on you only entering the total hours worked. If you
need to track each work session on the job, it starts getting complex!)

*Starts* getting complex?? You mean...we didn't get there yet??? Ahm...'k..

Have I totally confused you now?!

Well...this is very much in line with what I'm looking to do.
But....work....
backwards?? Ehh...Jeff......are you really sure on this part???

Good luck

Oh....well yeah....just throw all this out there and then say 'Good Luck'!!
Sheesh!

Jan

 

[Jeff Boyce]

Jan

Have I made some sense with this? I only bring this up to establish the
validity >

That sounds like a real-world example of why you need a history.

If my gray-matter is compiling this correctly, then this would provide for
the fact that there can be more than one Maintainer involved with the
repair, and they could be of various levels of job classifications, with
various pay rates. This is in fact exactly what can happen.

Again, real-world, as I suspected, can have more than one person
simultaneously or consecutively on a job, can have a job that takes a lot of
"calendar time" with much less "clock time", or could have someone who gets
upgraded during a job.

So, yes, job classification can play a very important factor in calculating
the cost of repairs. That is why I stated that your suggestion on this was a
very good point..promoting both brain matter and hair to another shade of
gray


*Starts* getting complex?? You mean...we didn't get there yet???

Ahm...'k..

It's all relative...

Have I totally confused you now?!

Well...this is very much in line with what I'm looking to do.
But....work....
backwards?? Ehh...Jeff......are you really sure on this part???

I can picture a query that starts with the Employee table, links to
EmployeeJobClass, then to the JobEmployeeJobClass table. E.g., find me John
Smith (Employee), and all related EmployeeJobClass records (John has been an
Apprentice, Journeyman and Master), and, for each of those, find the
JobEmployeeJobClass records (to get hours), and finally on to the Jobs table
(to find out where/name/?Supervisor/conditions/...). That's what I meant by
working backwards ... although I supposed you could try typing on your

Oh....well yeah....just throw all this out there and then say 'Good Luck'!!
Sheesh!

Perhaps you're right. Maybe I should close with something like:

Go get more coffee and sit back down for a spell... <G>!

Jeff Boyce
<Access MVP>

 

[Jan Il]

Hi Jeff!

Jan


That sounds like a real-world example of why you need a history.

Again, real-world, as I suspected, can have more than one person
simultaneously or consecutively on a job, can have a job that takes a lot of
"calendar time" with much less "clock time", or could have someone who gets
upgraded during a job.

I see the "calendar time" as being involved with a project where the work
would be spread out over a period of days or weeks perhaps, with various
Maintainers performing work at different times. Normally, this would be a
specially funded or Contractor assistance type of project, and calculating
and verifying all man-hours for labor costs would be required.

was
Ahm...'k..

It's all relative...

Ah..this is good....(??)

I can picture a query that starts with the Employee table, links to
EmployeeJobClass, then to the JobEmployeeJobClass table. E.g., find me John
Smith (Employee), and all related EmployeeJobClass records (John has been an
Apprentice, Journeyman and Master), and, for each of those, find the
JobEmployeeJobClass records (to get hours), and finally on to the Jobs table
(to find out where/name/?Supervisor/conditions/...). That's what I meant by
working backwards ... although I supposed you could try typing on your
keyboard with your back turned to the monitor... <g>

.....'k....I see the links here, and they relate...but....I don't recall
seeing the
JobEmployeeJobClass table before, just the trelEmployeeJobClass table. Do I
need a JobEmployeeJobClass table as well? If so, what fields would I need
in this table?

working backwards ... although I supposed you could try typing on your
keyboard with your back turned to the monitor... <g>

Oh...well..yea...I do have a cordless keyboard, so that should work. I make
me nervous watching me type anyway

Perhaps you're right. Maybe I should close with something like:

Go get more coffee and sit back down for a spell... <G>!

There ya go! ;-))

Jan

 

[Jeff Boyce]

Jan

Re-check an earlier response in this thread. I "invented" a
trelJobEmployeeJobClass table to relate a Job and an EmployeeJobClass (which
relates an Employee and a JobClass).

Jeff Boyce
<Access MVP>

 

[Jan Il]

Jeff -

Jan

Re-check an earlier response in this thread. I "invented" a
trelJobEmployeeJobClass table to relate a Job and an EmployeeJobClass (which
relates an Employee and a JobClass).

Ohhh....*that* trelJobEmployeeJobClass table....<g> My eyes musta got lost
in all the Employee, Job, Class mishmashing and stuff, guess they just
didn't see the first Job on that one. ;-) I already set up a table with all
those fields, so I'll just add the first 'Job' to the table name. (Whew)

Thanks Jeff.

Jan

 

[Jan Il]

Hi Jeff!

Jan

Re-check an earlier response in this thread. I "invented" a
trelJobEmployeeJobClass table to relate a Job and an EmployeeJobClass (which
relates an Employee and a JobClass).

Jeff Boyce
<Access MVP>

I have all the tables and they're all 'keyed' up as you instructed. I
already have an inspection list table, which lists all the various types of
company and regulatory agency required inspections that are to be performed
by both the Track and Wayside departments, and their ID codes. I used it to
create a tblJobList table, to which I added a few other jobs and their
descriptions, such as Repair, Outside Vendor Assistance, Construction
Inspections, Special Projects, Property Damage Repair and Vandalism. These
last two are basically for our Claims adjuster, for repairs to equipment
that is outside normal wear and maintenance. We normally will be reimbursed
for these repair costs ..but, we have been challenged in court on occasion
to verify the repair costs. Well...I set up a system in Excel to log these
man-hours some time ago, but, I don't see a purpose of having two systems,
when I can do this in Access, and all the information can be entered at
once, instead of a two step process.

Now...before I start creating all the queries, I think I should determine
just how I will need to sort this information. I am not sure if I need to
set up a separate form to track the jobs that are other than normal
maintenance and repair or inspections. I mean..I can add controls to my
current data entry forms to accommodate this additional information, extend
the filter forms to included filtering of this information for reports and
such, but, something is just not sitting well here with this idea. I don't
want to create a lot of unnecessary bloat to the db, or unnecessary
information for current reports that will not be necessary for Supervisors
or mid-management to review. I realize this is more a Forms issue, and I
will go there and post the necessary question, but, I wanted to check with
you first to see what you might recommend from a query point of view. I
would really appreciate your opinion on this.

Thank you for all you time and assistance, I really do appreciate it. If you
would just let me know about this last 'lil tee tiny piece of additional
information, I'll go away and leave you alone....;-))

Jan