I have an employee DB, some staff are on a ‘term time only’ contract e.g. they only work during school term times. I want to create a query showing only there working days, this will be used as a basis for many other things.
Tables I have are:
tbl_Emp_Details (employee details e.g. emp ID, name, age etc.)
tbl_Emp_Contract (contract details e.g. contract ID, Y/N current contract, Y/N works term time only, SchoolAuth
tbl_SchoolAuthority (lookup to the school authority they live within)
tbl_TermTimes (a table listing all the school holiday dates by school authority for each school year e.g 2022-2023
Using the above tables I can list each employee, the school authority aria they are in and all the holiday dates in any school year if they only work term times.
I would like to add to this each date of a school year then “Working” or “Not Working” depending on if there is a date in the ‘school holiday date’ column.
Any advice welcome, thanks