Calculate a running total based on the value of two colums

[caomhim]

I hope the title makes sense. I need to know how to subtract the value of one row and column from another.
I have a Access db with a table listing of employee holiday entitlement. A record of how many days holidays taken is kept in another table. The two tables are related by employee ID. How can I subtract the number of holidays taken way from an employees holiday entitlement so as to give me a running total of holiday entitlement the employee has left?

 

[JPMontreal]

First create a query like that:

SELECT HolidayTaken.EmployeNO, Sum(HolidayTaken.DayTaken) AS SumOfDayTaken
FROM HolidayTaken
GROUP BY HolidayTaken.EmployeNO;

then the second query like this:

SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName, [Employees]![HolidayEentitlement]-[TotalTaken]![SumOfDayTaken] AS DayLeft
FROM Employees INNER JOIN TotalTaken ON Employees.EmployeeID = TotalTaken.EmployeNO;


Jean-Paul
Montreal
mtljp2@sympatico.ca

 

[caomhim]

Many thanks JPMontreal. I tried out the first query but couldn't remember how to nest the second query. Below you'll see the nested query I wrote, but could not get it to run.

SELECT Absence.AbEmpno, Sum(Absence.Duration) AS SumOfDuration
FROM Absence
GROUP BY Absence.AbEmpno;
(SELECT Employees.EmpRefNo, Employees.EmpLname, Employees.EmpFname, [Employees]![HEntitlement]-[Absence]![SumOfDuration] AS DaysLeft
FROM Employees INNER JOIN Absence ON Employees.EmpRefNo = Absence.AbEmpno;
)

Not sure that I'm correct though. Please can you advise.
once again - many thanks!!
Caomhim

 

[gormcd]

Create a new query.
Add the two tables.
Join employee id if it is not already done.
Bring down employee ID from the holiday entitlement table.
In a new field enter the following:

HolidayLeft:[HolidayEntitlement]-[HolidayTaken]

If you have any questions, please let me know.

Thanks.

gormcd

 

[caomhim]

Hello GormCd

Many thanks for your assistance. I tried what you suggested. It worked but it subtracted the Holidays Taken was subtracted from Holiday Entitlement for each instance of a holiday taken. (If what I am saying makes any sense).Whats needed is for a query which adds up all the holiday taken and then takes this cumulative sum away from the employees holiday entitlement. Grateful if any one can advise.

regards
Caomhim