how to treat pay increases in employee tables

[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

 

[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.

 

[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

 

[Guest]

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

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

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