Jamie, the usual way to solve this is to record both the hourly rate and the
overtime rate for each employee. Then lookup these values at the time when
the record is added, and store the value (because one day the pay rates
might change).
This example assumes that there can only ever be 2 rates of pay for an
employee. (It needs another layer if there are other rates such as
time-and-a-half, double-time, etc.)
In your Employee table, store the $/hr the person earns for normal and for
overtime work. The Employee table will have these fields:
EmployeeID AutoNumber primary key
Surname Text
FirstName Text
HourlyRate Currency
OvertimeRate Currency
In the Wage table, store these fields:
WorkDate DateTime the date worked
EmployeeID Number foreign key to Employee.EmployeeID
IsOverTime Yes/No
HourlyRate Currency
Hours Number (Double) How many hours
You will use a combo for selecting employee.
It's RowSource property will be a query statement like this:
SELECT EmployeeID, HourlyRate, OvertimeRate
FROM Employee ORDER BY Surname, FirstName;
Now use the AfterUpdate event procedure of the combo so that it assigns the
pay rate:
Private Sub EmployeeID_AfterUpdate()
With Me.EmployeeID
If Not IsNull(.Value) Then
If Me.IsOverTime.Value Then
Me.HourlyRate = Me.Column(1)
Else
Me.HourlyRate = Me.Column(2)
End If
End With
End With
End Sub
In the AfterUpdate event of the IsOvertime check box, call the same code:
Private Sub IsOverTime_AfterUpdate()
Call EmployeeID_AfterUpdate
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Jamie Pittman via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message
news:6f******************************@AccessMonste r.com...
I do understand to make them one table, but the issue I keep coming up with
is, when the overtime is entered I need to caculate that fied with a
seperate rate. If I use a (yes or no)box I then have trouble with null
values in the caculations. When I made the first database, I made drop
select list to use the customers, buildings, and employees, so I wouldn't
run into the problem with mis-spellings. What do you think I should
proceed with next?????
