467,161 Members | 991 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,161 developers. It's quick & easy.

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
Nov 12 '05 #1
  • viewed: 2048
Share:
1 Reply
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

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Joe Bongiardina | last post: by
3 posts views Thread by luscus | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.