*Complex query/calculations

[JMorrell]

I have a form which feeds TblEmpLeave with Employee Leave Time (time taken off for Administrative, Annual, Sick, Compensation leave). I have LeaveDate, LeaveType, LeaveHours fields on this form. Any employee can have multiple entries in the table 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 they've accumulated throughout the year, and an accumulation factor for 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].[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]

I'm sure I know what I want to do (which is a good start), but not sure how to go about it

thanks in advance
jl

 

[JMorrell]

I was able to handle this programming technique in SAP's ABAP/4, but how is it done in Access/VB

Please advise.