Calculating fields in query

[jlm]

I have a form which feeds table (TblEmpLeave) of Employee Leave Time
(time taken off for Administrative, Annual, Sick, Compensation leave).
I have EmpID, LeaveDate, LeaveType, LeaveHours fields on this form.
Any employee can have multiple entries in the table (key fields are
EmpID and LeaveID) for multiple dates (John Doe can take 3 days annual
leave, then take 3 days sick leave in any given month.

I have a BeginningBalance of hours that they have accumulated
throughout the year, and an accumulation factor of Annual and Sick
leave.

I want to subtract from the BeginningBalance of LeaveType(s) for each
record in TblEmpLeave. As such, I can calculate the first occurance
of leave, but if the employee has another record in TblEmpLeave, I'm
having problems subtracting from the new value of BeginningBalance.

Sick Leave example:
for first occurance of SickLeave taken by an Employee that is appended
to TblEmpLeave;
[TblEmp].[SickBalance] = [TblEmp].[SickBalance] -
[TblEmpLeave].[TblEmpLeave].[SickLeaveHoursUsed]

for second and subsequent occurances of same employee in TblEmpLeave;
[TblEmp].[SickBalance] =
[TblEmp].[SickBalance]-[TblEmpLeave][SickLeaveHoursUsed].

finally, at the end of the month, for each record in TblEmp, after
calculating all leave used;
[TblEmp].[SickBalance] = [TblEmp].[SickBalance] +
[TblEmp].[SickLeaveAccumulationFactor].

Values are not calculating as I think they should. What am I missing
here?
thanks in advance.
jlm

[PaulT]

for the 'second and subsequent', you may need to get the SickBalance
where max(empID) so that you do get the 'balance-at-last-occurence'.

-Paul

je************@hotmail.com (jlm) wrote in message news:<7b**************************@posting.google. com>...

I have a form which feeds table (TblEmpLeave) of Employee Leave Time
(time taken off for Administrative, Annual, Sick, Compensation leave).
I have EmpID, LeaveDate, LeaveType, LeaveHours fields on this form.
Any employee can have multiple entries in the table (key fields are
EmpID and LeaveID) for multiple dates (John Doe can take 3 days annual
leave, then take 3 days sick leave in any given month.

I have a BeginningBalance of hours that they have accumulated
throughout the year, and an accumulation factor of Annual and Sick
leave.

I want to subtract from the BeginningBalance of LeaveType(s) for each
record in TblEmpLeave. As such, I can calculate the first occurance
of leave, but if the employee has another record in TblEmpLeave, I'm
having problems subtracting from the new value of BeginningBalance.

Sick Leave example:
for first occurance of SickLeave taken by an Employee that is appended
to TblEmpLeave;
[TblEmp].[SickBalance] = [TblEmp].[SickBalance] -
[TblEmpLeave].[TblEmpLeave].[SickLeaveHoursUsed]

for second and subsequent occurances of same employee in TblEmpLeave;
[TblEmp].[SickBalance] =
[TblEmp].[SickBalance]-[TblEmpLeave][SickLeaveHoursUsed].

finally, at the end of the month, for each record in TblEmp, after
calculating all leave used;
[TblEmp].[SickBalance] = [TblEmp].[SickBalance] +
[TblEmp].[SickLeaveAccumulationFactor].

Values are not calculating as I think they should. What am I missing
here?
thanks in advance.
jlm