I currently have a database, in Access, which keeps track of employee's days off. I would like to be able to calculate the total days taken by each employee. The current database has three tables, Dates, Names and DaysExcluded.
The Dates table lists the date range an employee has had off, note that if the EndDate is empty, then it assumed that they only took 1 day off, as well as listing if the day was approved.
The Employee table lists each employee's name and department.
The DaysExcluded lists the dates that shouldn't count towards days off, as they are public holidays.
I would like a table called Result that lists, EmployeeID, FirstName, LastName, Department, DaysApproved and DaysNotApproved. Please note that only weekdays should be counted as a day off, i.e. if an employee is off sick Friday, Saturday, Sunday and Monday, then it will only count as 2 days off.
I have attached a spreadsheet which lists three sample tables and how the results should look.
Please note that I am looking for a quick turn around time on this project.