how to treat pay increases in employee tables


G

Guest

new to access, and have set up and everything working the way I think it
should.
but when I give an employee a pay raise how do I keep old rate from changing
in tables and querys
 
Ad

Advertisements

D

Duane Hookom

You either store the pay rate with related records or you create a pay rate
history table that suggests when a pay rate change occured.
 
G

Guest

This is analogous to unit prices for products in the sample Northwind
database. If you look at the AfterUpdate event procedure of the of the
ProductID combo box on the Orders Subform you'll se that it calls the DLookup
function to get the current unit price from the Products table. This value
is assigned to the UnitPrice column in the subform's underlying Order details
table. Consequently, while the Products table stores the current UnitPrice
values the values in the Order Details table remain static if the UnitPrice
in products changes. In your case the current PayRate would be stored in the
Employees table against each employee, and looked up and stored in a Payments
table.

The alternative would be to have a separate EmployeePayRates table with
foreign key EmployeeID column, a DateApplied column and a PayRate column.
This would store all rates for each employee over their employment history.
You could then look up the current rate in a computed control on a form with
something like this:

=DLookup("PayRate", "EmployeePayRates" ,"EmployeeID = " & [EmployeeID] & "
And DateApplied = #" & Format(DMax("DateApplied", "EmployeePayRates",
"EmployeeID = " & [EmployeeID] & " And DateApplied <= #" &
Format([PaymentDate],"mm/dd/yyyy") & "#), "mm/dd/yyyy") & "#")

In a query you'd have something like:

SELECT EmployeeID, PaymentDate, PayRate * HoursWorked AS PaymentAmount
FROM Payments, EmloyeePayRates AS EPR1
WHERE EPR1.EmployeeID = Payments.EmployeeID
AND EPR1.DateApplied =
(SELECT MAX(DateApplied)
FROM EmploymentPayRates As EPR2
WHERE EPR2.EmployeeID = Payments.EmployeeID
AND EPR2.DateApplied <= Payments.PaymentDate);

whwre Payments is a table with columns EmployeeID and HoursWorked.

Ken Sheridan
Stafford, England
 
Ad

Advertisements

G

Guest

Thanks I'll be trying today
I'll let you know
Thanks again
Larry




Ken Sheridan said:
This is analogous to unit prices for products in the sample Northwind
database. If you look at the AfterUpdate event procedure of the of the
ProductID combo box on the Orders Subform you'll se that it calls the DLookup
function to get the current unit price from the Products table. This value
is assigned to the UnitPrice column in the subform's underlying Order details
table. Consequently, while the Products table stores the current UnitPrice
values the values in the Order Details table remain static if the UnitPrice
in products changes. In your case the current PayRate would be stored in the
Employees table against each employee, and looked up and stored in a Payments
table.

The alternative would be to have a separate EmployeePayRates table with
foreign key EmployeeID column, a DateApplied column and a PayRate column.
This would store all rates for each employee over their employment history.
You could then look up the current rate in a computed control on a form with
something like this:

=DLookup("PayRate", "EmployeePayRates" ,"EmployeeID = " & [EmployeeID] & "
And DateApplied = #" & Format(DMax("DateApplied", "EmployeePayRates",
"EmployeeID = " & [EmployeeID] & " And DateApplied <= #" &
Format([PaymentDate],"mm/dd/yyyy") & "#), "mm/dd/yyyy") & "#")

In a query you'd have something like:

SELECT EmployeeID, PaymentDate, PayRate * HoursWorked AS PaymentAmount
FROM Payments, EmloyeePayRates AS EPR1
WHERE EPR1.EmployeeID = Payments.EmployeeID
AND EPR1.DateApplied =
(SELECT MAX(DateApplied)
FROM EmploymentPayRates As EPR2
WHERE EPR2.EmployeeID = Payments.EmployeeID
AND EPR2.DateApplied <= Payments.PaymentDate);

whwre Payments is a table with columns EmployeeID and HoursWorked.

Ken Sheridan
Stafford, England

lairdjr said:
new to access, and have set up and everything working the way I think it
should.
but when I give an employee a pay raise how do I keep old rate from changing
in tables and querys
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top