working or not based on school term time

[Ant_Snell]

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

[kd2017]

For precision's sake please define "each date of a school year".

I'm thinking you may want a calendar table that just has a row for every day. Should be easy to populate the table for an arbitrary date range using vba. Then instead of storing date datatypes for holidays etc you would store foreign keys that link back to the calendar table.

https://www.google.com/search?client...calendar+table

[CJ_London]

I'm struggling with this

tbl_TermTimes (a table listing all the school holiday dates by school authority for each school year e.g 2022-2023

shouldn't that be holiday times?

Also need to know what that table looks like - is it just a list of dates or two fields - dateFrom and dateTo

[Ant_Snell]

kd2017 definition of "each date of a school years" = 365 rows each containing a date in the school year (start of school year September to end of school year September as apposed to an actual year of 1st Jan to 31 Dec. The actual dates could differ depending on the school Authority's team times but this is defined in the tbl_schoolAuthority and related to the employee via the tbl_Emp_Contract

CJ_London tbl_TermTimes consist of:
TermTime_ID AutoNumber
SchoolAuthority Number lookup to the authority e.g. Sheffield CC
SchoolYear Number lookup to the school year e.g. 2022-2023
SchoolHolidayDates Date/Time
Description Number lookup to the holiday name e.g. Summer half term

This table is populated at the beginning of each school year with the dates of every holiday (one record per date) for each authority. this is related back to the employee using the employee contract table that shows what authority they are under. This way if authority's have different term times, each employee will be linked to the correct set of dates.

[CJ_London]

thanks for the clarification

to determine working/not working left join your table (name not provided but described as - "definition of "each date of a school years" = 365 rows each containing a date in the school year") to the tbltermTimes on the date

where the termtimes date field is null - that will be 'working', and where populated, that will be 'not working'.

This should be calculated as and when required using a query, not stored