I need to create a simple query in MS Access to calculate employee entitled leave, subtract from the sum of leave taken (number of days) and give the available leave days remaining.
The situation you describe sounds like you have two tables. The first table, employees, has one row per employee, and a column for entitled leave.
empid empname emptitle empentitled 24 P.H.Boss Manager 15 36 K.Brown Analyst 10 44 E.R.Win Modeller 10 58 H.McGillicuddy DBA 10
The second table, leavetaken, has employee leave taken, and let's assume this is accomplished simply by recording the date the employee was away.
empid leavedate 24 2002-09-13 36 2002-09-13 58 2002-09-17 58 2002-09-18 58 2002-09-19 24 2002-09-20 36 2002-09-20 58 2002-09-24
Assuming there's one row per employee per day of leave taken, your query would be:
select empname , ( empentitled - count(leavedate) ) as empremaining from employee left outer join leavetaken on employee.empid = leavetaken.empid group by empname , empentitled
This query features a left outer join and an aggregate function. The left outer join is required so that all employees are listed, including any like E.R.Win who had no leave taken. An inner join would omit employees which have no matching rows in the leavetaken table. Grouping is required, to produce the aggregate result for each employee. In this case all we need is a count, since we assumed that each row represents one day.
The trick with using the COUNT() aggregate function to produce the correct count in a left outer join is to use COUNT(column) instead of COUNT(*). All aggregate functions except COUNT(*) ignore nulls. In a left outer join, unmatched rows have nulls in any column from the right table. Therefore, COUNT(leavedate) has to produce a zero for employees that have no matching row from the leavedate table. If we use COUNT(*), we get the count of the number of rows, regardless of whether the employee row has a matching leavetaken row. Since grouping occurs at the employee level, COUNT(*) would yield a 1 for every employee that has no matching rows, but otherwise would produce the right answer when the employee does have matching rows.
Suppose that, instead of one row per day of leave taken, the leavetaken table has a start date and end date:
empid leavestart leaveend 24 2002-09-13 2002-09-13 36 2002-09-13 2002-09-13 58 2002-09-17 2002-09-19 24 2002-09-20 2002-09-20 36 2002-09-20 2002-09-20 58 2002-09-24 2002-09-24
Now we must use SUM() instead of COUNT(). We're still after the number of days, which we can get individually for each row by doing a date calculation. Note that when the start and end dates are equal, we assume that's one day of leave, but since subtracting the same date from itself would yield zero, we have to add one:
leaveend - leavestart + 1
Fortunately, MS Access automatically produces an answer in days whenever we subtract one date from another, but we could also use the DATEDIFF() function.
So the solution for SUM() would be:
select empname , ( empentitled - sum( case when leavestart is null then 0 else leaveend - leavestart + 1 end ) ) as empremaining from employee left outer join leavetaken on employee.empid = leavetaken.empid group by empname , empentitled