Hourly rate changes in payroll system

[Sierras]

Hi

Just trying to get ideas as to how to get around the problem of hourly
rate changes in a payroll type database. If I place the hourly rate of
the employee in the employee table, then it would be easy to get the total
amount to pay the person based on the hours worked. But that would mess
up my history totals when running a query.

I guess I need to capture the rate being used for the employee for the
week worked and store it in a table so that any rate changes in the furure
won't affect historical data.

Any ideas or examples of how to get around this?
Thanks

 

[Barry Gilbert]

Create a Rates table. In the table, have a column for the employee Id
(related to the employees table). Also have columns for rate and effective
from date and effective to date. This will allow you to set a rate to be
effective in the future. The effective date is the date the rate went into
effect. This table will also need a seperate primary key, like an autonumber.
When you query the hours worked/rate, you can find historical rates.

HTH,
Barry

 

[Klatuu]

Barry's solution is correct; however, a more simplistic approach would be a
little denormalization. In you pay History table, just a field [PAID_RATE].
When you process payroll update that field with the rate at which the
employee was paid.

I am sure there will be some purists who will flame me for this suggestion,
but I think purists sometimes don't read the entire book. The skip the part
about when and why you should denormalize. I would argue this is a good case
for denormalization.

 

[Barry Gilbert]

Simpler and perfectly reasonable.

Barry's solution is correct; however, a more simplistic approach would be a
little denormalization. In you pay History table, just a field [PAID_RATE].
When you process payroll update that field with the rate at which the
employee was paid.

I am sure there will be some purists who will flame me for this suggestion,
but I think purists sometimes don't read the entire book. The skip the part
about when and why you should denormalize. I would argue this is a good case
for denormalization.

Hi

Just trying to get ideas as to how to get around the problem of hourly
rate changes in a payroll type database. If I place the hourly rate of
the employee in the employee table, then it would be easy to get the total
amount to pay the person based on the hours worked. But that would mess
up my history totals when running a query.

I guess I need to capture the rate being used for the employee for the
week worked and store it in a table so that any rate changes in the furure
won't affect historical data.

Any ideas or examples of how to get around this?
Thanks

 

[Klatuu]

Thank you, Barry. I was not attacking your solution at all. If you adhere
to the rules of database normalization, your's would be the correct solution.
In some cases, however, it does make sense to bend the rules.

Simpler and perfectly reasonable.

Barry's solution is correct; however, a more simplistic approach would be a
little denormalization. In you pay History table, just a field [PAID_RATE].
When you process payroll update that field with the rate at which the
employee was paid.

I am sure there will be some purists who will flame me for this suggestion,
but I think purists sometimes don't read the entire book. The skip the part
about when and why you should denormalize. I would argue this is a good case
for denormalization.

Hi

Just trying to get ideas as to how to get around the problem of hourly
rate changes in a payroll type database. If I place the hourly rate of
the employee in the employee table, then it would be easy to get the total
amount to pay the person based on the hours worked. But that would mess
up my history totals when running a query.

I guess I need to capture the rate being used for the employee for the
week worked and store it in a table so that any rate changes in the furure
won't affect historical data.

Any ideas or examples of how to get around this?
Thanks

 

[Sierras]

Thanks. I think a paid history table is what I'm looking for as long the
field would populate itself with the currect rate rather than having to
edit each record seperately. Would it be possible to have an employee
table with the name and the current rate and have the history table
populate itself with this rate automatically? I'm not sure how to do this
automatically with just a click of a process button.

Thanks

Barry's solution is correct; however, a more simplistic approach would
be a
little denormalization. In you pay History table, just a field
[PAID_RATE].
When you process payroll update that field with the rate at which the
employee was paid.

I am sure there will be some purists who will flame me for this
suggestion,
but I think purists sometimes don't read the entire book. The skip the
part
about when and why you should denormalize. I would argue this is a good
case
for denormalization.

Hi

Just trying to get ideas as to how to get around the problem of hourly
rate changes in a payroll type database. If I place the hourly rate of
the employee in the employee table, then it would be easy to get the
total
amount to pay the person based on the hours worked. But that would mess
up my history totals when running a query.

I guess I need to capture the rate being used for the employee for the
week worked and store it in a table so that any rate changes in the
furure
won't affect historical data.

Any ideas or examples of how to get around this?
Thanks

 

[Klatuu]

Assuming you have a form where you update the pay rate for an employee, you
could put code in the form's after update event to create a new record in the
history table. In the history table, you will also need an "effective date"
so any payroll history reporting would know which history record to match up
with.

Now, here is why I suggest adding a field to the payroll posting table
rather than having a rate history table. You queries or code used in payroll
history would have to look in the history file for an employee, determine
whether the record in the history file is the correct record to use for the
date paid based on the history record's effective date. It may have to find
the correct record in history or if none match then use the current employee
record pay rate. This will complicate your reporting. If, on the other
hand, you had a field in the payroll posting table, you would not have to
worry about finding the correct rate for that payroll posting.

Thanks. I think a paid history table is what I'm looking for as long the
field would populate itself with the currect rate rather than having to
edit each record seperately. Would it be possible to have an employee
table with the name and the current rate and have the history table
populate itself with this rate automatically? I'm not sure how to do this
automatically with just a click of a process button.

Thanks

Barry's solution is correct; however, a more simplistic approach would
be a
little denormalization. In you pay History table, just a field
[PAID_RATE].
When you process payroll update that field with the rate at which the
employee was paid.

I am sure there will be some purists who will flame me for this
suggestion,
but I think purists sometimes don't read the entire book. The skip the
part
about when and why you should denormalize. I would argue this is a good
case
for denormalization.

Hi

Just trying to get ideas as to how to get around the problem of hourly
rate changes in a payroll type database. If I place the hourly rate of
the employee in the employee table, then it would be easy to get the
total
amount to pay the person based on the hours worked. But that would mess
up my history totals when running a query.

I guess I need to capture the rate being used for the employee for the
week worked and store it in a table so that any rate changes in the
furure
won't affect historical data.

Any ideas or examples of how to get around this?
Thanks

 

[Ron2006]

Another field that is often affected by this type or change is Employee
name. Depending on how long you keep records of non-current employees,
and how long you want to keep records of pay checks, the employee
information may not be there. Then again if the emplyee changes names,
do you want the "History" to show you the actual name that was on that
original report and payroll check or do you want it to show the current
employee name.

Things that bite you when auditing gets in the picture and is trying to
do research. It is not fun trying to find an employee's time card under
a new name when it was printed and used using the old name. All of this
depends on the user requirements/ knowledge and HOW they are expecting
to use the history report.

 

[Klatuu]

I think what you are saying is that the Employee Number or ID should be used
instead of the name. Mary Smith may become Mary Fronmeyer at some point in
time and cause a disconnect in history reporting. Good Point.

 

[Ron2006]

Yes, part of it is that. The other point is that the name (and payrate
which is what the question was about) would be stored so that a name
change would not change the report from when it was printed originally.

I have found that in general, it is better to freeze all of that
historical type of information and try NOT to have to duplicate
calculations. Many moons ago I worked for a company that changed the
way that they caluclated overtime. If something like that happens, and
you are trying to print history, you have to not only update your
current payroll calculation steps but also all of your historical
presentations.
Sort of like trying to reprint an invoice for parts when the price per
part has changed (and/or the part description has changed.)

 

[Sierras]

I am trying to make a table that captures the employee current rate and
hours and use this table for both the amount to pay the employee as well
as historical info for the rate used at the time.

I have 2 tables. One is the employee table which shows the name and
current rate.
The other table is the hours paid table which shows the rate paid as well
the hours worked.
I would like to have the rate paid field to automatically pick up the
current rate from the employee table as the default for new records. So
that I don't have to write this value in each record every time. Is there
any easy way of doing this?

Thanks

 

[Ron2006]

One way is to make a display query that joins these two tables so that
you have all of the fields you need, along with a new field called
"Pay" and one for the effective date. In the query wizard you can
define "pay" as something like Pay:[payratefromthisquery] *
[payhoursfromthisquery]

Once you have done this and tried it out and it seems to have all
calculations correct and all the information that you require. then
change the query type to an append query, and have it append to a table
with that definition (run once a create table query).

Now change any of the other queries and reports that need to know the
pay detail to use that table. Be sure to have all the fields you want
to remain static in that query/table. That table will now become the
source for all reports/queries seeking to know the payhistory and
nothing in that table should be modifiable.

Hope this gives you some ideas.

Ron