Recent content by Jenklot

Let me ponder on this... Thanks

Could you please explain on the expression that you used... NZ((SELECT SUM(LF.Total_Days) FROM [Leave Form] as LF WHERE LF.Staff_ID = PLD.Staff_ID AND LF.Leave_Type IN ('AL', 'EL')),0) as AnnualLeave, I'm a bit confuse on the usage of "nz", "select sum" and "in"

Thanks, you really help me out big time, by the way is it ok if I PMed you for future reference or any questions regarding Access in particular?

I just add [leave_balance] in front and everything seems right [leave_balance] + [entitlement_leave] - NZ((SELECT QLF.DaysTaken FROM qryLeaveForm as QLF WHERE QLF.Staff_ID = PLD.Staff_ID AND QLF.leaveType = 'AL'),0) as AnnualLeave, Will post result after more testing done

Those who got "leave balance" from last year will get their total leave wrong, example a person with a balance of 2 days will get their answer wrong by 2. last year balance = 2 days this year leave entitlement = 18 days total accumulate leave = 20 days al & el taken so far = 10 days it should...

Thanks, I give it a try...

There is 3 types of leave as listed below where Emergency leave (Non Approved Leave Application) is under the same category with Annual leave (Approved Leave) and Medical & Compassionate stands on its own. No Leave Type 1 Annual/ Emergency Leave 2 Medical Leave...

Jal, Ok, AL stands for Annual Leave, MC for Medical Leave & Emergency leave for EL. AL & EL are under the same category, meaning that taking EL will decrease total leave balance.

Could someone please help me to solve this query? How can I create a query that’s enabled me to check leave balance of each/ all type of leave al & el (same leave category), mc & cl at the end of every week/ month based on staff id? The Excel file is currently used for entering leave application...