- Why do you have some null StaffIDs? I deleted those records to avoid distractions.
- WARNING: I rarely get a query right on the first try. When I'm helping someone, I don't do the testing. It's YOUR RESPONSIBILITY to test it IN EVERY POSSIBLE WAY and then let me know if there is a problem. Under no circumstances should you assume that I got it right. Ok, here we go.
- Here is a base query. Paste it into SQL View and save it under the name qryLeaveForm. It's just the totals from table LeaveForm and it groups EL and AL together.
SELECT staff_ID, IIF(leave_Type = 'EL', 'AL', leave_Type) as leaveType, Sum(Total_Days) as DaysTaken FROM [LEAVE FORM]
GROUP BY staff_ID, IIF(leave_Type = 'EL', 'AL', leave_Type)
I make use of the above query (qryLeaveForm) - I use it like a table - in the query below. I do three subtractions, as you can see below (I put a blank line in between each so you can see what I'm doing).
(1) entitlement_Leave. I substraced the 'AL' + 'EL' total.
(2) compassionate_leave. I subtracted the 'CL' total
(3) mc_leave. I subtracted the 'MC' total.
As you can see I assumed there are four codes
AL
EL
CL
MC
And here is the query:
SELECT Staff_ID,
entitlement_leave - NZ((SELECT QLF.DaysTaken FROM qryLeaveForm as QLF WHERE QLF.Staff_ID = PLD.Staff_ID AND QLF.leaveType = 'AL'),0) as AnnualLeave,
compassionate_leave - NZ((SELECT QLF.DaysTaken FROM qryLeaveForm as QLF WHERE QLF.Staff_ID = PLD.Staff_ID AND QLF.leaveType = 'CL'),0) as CompassionateLeave,
mc_leave - NZ((SELECT QLF.DaysTaken FROM qryLeaveForm as QLF WHERE QLF.Staff_ID = PLD.Staff_ID AND QLF.leaveType = 'MC'),0) as MedicalLeave
FROM [Personal Leave Data] as PLD
Here I use the keyword NZ to subtract zero as to avoid subtracting 'null' - nulls exist in cases where the employee hasn't yet used any of his days.
I did nothing year-specific. Instead I made things easier on myself by assuming that all the data in the tables would be for the current year.
And I did nothing with "leave balance from last year."
