By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,161 Members | 1,020 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,161 IT Pros & Developers. It's quick & easy.

Calculating fields in query

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.