I have a table with dates for this academic year. The table is structured as follows:
Date_field, teachingDay
The date field contains dates starting 01st August 2019 and ending 31st July 2020. The teachingDay field contains either 1 or 0.
1 - it is a teaching day
0 - non-teaching day (including weekends, public holidays, our organisation-specific days off for students, etc)
I also created a query containing only holidays.
I am trying to do two things:
1. Count the number of sessions for a class. Eg. If a class starts on Friday, 23rd November 2019 and ends on a Friday on a specific date in Feb 2020. I want to know how many sessions will students attend ( number of TEACHING Fridays between the two dates including the start and end dates).
I tried datediff but it does not give you an option to exclude holidays. I also tried to use a VBA script (workingDay) linked elsewhere which requires a tblHolidays to exclude days off but it counts all working days between the two days, not just the days where a session takes place. Worse still sometimes a session may take place 2 days a week (eg on Mondays and Tuesdays).
Since I have got the table that shows clearly which days are teaching days for the year ahead, I think my best bet would be something along the lines:
If a course starts on Friday, 23 November and ends on a Friday, a date in Feb 2020, count the number of Fridays between those two dates (inclusive) where the teachingDay = 1.
That would also work if the course takes place on 2 days a week
How would I start it?
2. How would I generate a query with all those dates so a teacher can distribute a list of teaching dates of a particular cohort among students
Thank you
Date_field, teachingDay
The date field contains dates starting 01st August 2019 and ending 31st July 2020. The teachingDay field contains either 1 or 0.
1 - it is a teaching day
0 - non-teaching day (including weekends, public holidays, our organisation-specific days off for students, etc)
I also created a query containing only holidays.
I am trying to do two things:
1. Count the number of sessions for a class. Eg. If a class starts on Friday, 23rd November 2019 and ends on a Friday on a specific date in Feb 2020. I want to know how many sessions will students attend ( number of TEACHING Fridays between the two dates including the start and end dates).
I tried datediff but it does not give you an option to exclude holidays. I also tried to use a VBA script (workingDay) linked elsewhere which requires a tblHolidays to exclude days off but it counts all working days between the two days, not just the days where a session takes place. Worse still sometimes a session may take place 2 days a week (eg on Mondays and Tuesdays).
Since I have got the table that shows clearly which days are teaching days for the year ahead, I think my best bet would be something along the lines:
If a course starts on Friday, 23 November and ends on a Friday, a date in Feb 2020, count the number of Fridays between those two dates (inclusive) where the teachingDay = 1.
That would also work if the course takes place on 2 days a week
How would I start it?
2. How would I generate a query with all those dates so a teacher can distribute a list of teaching dates of a particular cohort among students
Thank you