Number / list of sessions between 2 dates (1 Viewer)

Ports

Registered User.
Local time
Today, 04:07
Joined
Jun 30, 2019
Messages
64
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:07
Joined
May 7, 2009
Messages
19,169
you need an aggregate query to do that:
Code:
select "No of Sessions from " & [startDate] & " up to " & [endDate]", 
     count("1") As [No of Sessions] 
from your table 
where [Date_field] between [startDate] and [endDate] 
and [teachingDay] =  1
group by "No of Sessions from " & [startDate] & " up to " & [endDate]"

this will give you the total sessions.
for the date listing:
Code:
select [teachingDate] 
from yourTable
where [teachingDate] between [startDate] and [endDate] 
and [teachingDay] = 1;
 

Ports

Registered User.
Local time
Today, 04:07
Joined
Jun 30, 2019
Messages
64
Thanks a lot. Two questions:
TblDates: reference table with all the dates and the teachingday field
QryCourseCodes : contains course codes with the actual start /end dates

Do I just create a relationship between the date field in tblDates and the startDate (and separately endDate) in the qryCourseCodes?

Secondly, am I right that the code above calculates the number of all working dates between, and not only the specific days of the week where the course takes place (eg. Fridays only) ?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:07
Joined
May 7, 2009
Messages
19,169
better if you some snapshot of your tables before we proceed.
we might be leading to a wrong direction.
 

Ports

Registered User.
Local time
Today, 04:07
Joined
Jun 30, 2019
Messages
64
better if you some snapshot of your tables before we proceed.
we might be leading to a wrong direction.


Right, I was trying to do as much as I could before I reply. I've made some progress but not much. Mostly on my second point above.



I'm attaching my test database to better illustrate it.


Form: CreateCourseCode: One of the controls it has is "DayName" listBox that lets you select multiple days of the week (eg. Monday and Wednesday). What gets saved in the table ("CourseCodeGenerator") in a field called "SessionDays" is a string: eg. "1" for Monday, "1,2" for Monday and Tuesday, etc.


Now, in the query ("qryCourseCodes") I'm trying to split "SessionDays" into separate query columns, each for one day. Although the delimiter is known ",", the length of the string is going to be variable depending on how many days a course takes place on.


This information is needed to generate the list of all the sessions. I kind of have get it working (but only for sessions that would take place on 1 day a week).

a). You choose the course you want list of sessions for in the ListSessions form and press a button on that form.

b). That opens a query ("qrySessionList") that uses the ListSessions form controls for filtering.


How would I extract the SessionDays from the query to include all the sessions?


Thank you.
 

Attachments

  • Generator - test_v1.zip
    87.1 KB · Views: 139

mike60smart

Registered User.
Local time
Today, 11:07
Joined
Aug 6, 2017
Messages
1,899
Hi

I would have thought that storing the Actual Teaching Day as a Record would be easier to manage.

Rather than storing Teaching Days as a String.
 

Ports

Registered User.
Local time
Today, 04:07
Joined
Jun 30, 2019
Messages
64
Hi

I would have thought that storing the Actual Teaching Day as a Record would be easier to manage.

Rather than storing Teaching Days as a String.


I agree but, as I've googled, if a listBox has multiple values (eg. Monday and Tuesday), it does not save the value to table fields.
 

June7

AWF VIP
Local time
Today, 03:07
Joined
Mar 9, 2014
Messages
5,423
Saving multiple records for values selected in multi-select listbox requires VBA looping through listbox selected items.
 

mike60smart

Registered User.
Local time
Today, 11:07
Joined
Aug 6, 2017
Messages
1,899
Hi

Is the attached Report what you are trying to achieve??

Course Days.PNG
 

Ports

Registered User.
Local time
Today, 04:07
Joined
Jun 30, 2019
Messages
64
Hi

Is the attached Report what you are trying to achieve??

View attachment 76203




Thanks. Not neccesarily. More a list of all dates for 1 course. If you choose a course in the combo box of ListSessions Form and click on the button present in the form, it'll run the qrySessionList producing a list of dates/days that particular course will run. The problem is that it does not take into account the fact if a course takes place 2-3 times a week. It just takes the start date (eg. 23rd August, which is a Friday) and counts number of teaching Fridays between the Start and End dates. They way it knows if if it's a teaching day or a holiday is by checking the "OperatingSchedule" table.


Hope that makes sense.
 

Ports

Registered User.
Local time
Today, 04:07
Joined
Jun 30, 2019
Messages
64
Saving multiple records for values selected in multi-select listbox requires VBA looping through listbox selected items.


Thanks. I saw some examples online but was hoping there must be a simpler way. I mean so much code just to save a couple of values in records. Will revisit it.
 

Ports

Registered User.
Local time
Today, 04:07
Joined
Jun 30, 2019
Messages
64
The report will be something like that the attached screenshot.
 

Attachments

  • report_access.png
    report_access.png
    20.9 KB · Views: 149

June7

AWF VIP
Local time
Today, 03:07
Joined
Mar 9, 2014
Messages
5,423
The 'simpler way' is user does data entry one record at a time with a single-select listbox or combobox. The more 'user friendly' the more code.

Getting a query to output records where there really aren't any in table is complicated and often involves a Cartesian query which can perform slowly with large dataset. An alternative is to write records to a 'temp' table - table is permanent but records are temporary. Again, more code.

Here is example of a query that will generate a record for each day within a date range (copy/paste into SQLView of query builder):
Code:
PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT DISTINCT Format([StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1, "mm/dd/yyyy") AS MDY, 
Weekday([StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1, 2) AS WkDy,
Format([StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1, "yyyy q") AS YearQtr
FROM MSysObjects AS Ones, MSysObjects AS Tens, MSysObjects AS Hundreds
WHERE [StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1 Between [StartDate]-1 And [EndDate];
A multi-value field might be preferable to saving a CSV string. And I NEVER use multi-value field. This is about as simple data entry as conventional approach. https://support.office.com/en-us/ar...-queries-6f64f92d-659f-411c-9503-b6624e1e323a
Here is a query using a multi-value field for SessionDays and calculates dates for the weekday numbers of week for given date.
Code:
SELECT Provider, LAIM, StartDate, SessionDays.Value, [StartDate]-Weekday([StartDate],2)+([SessionDays].[Value]) AS Dte
FROM CourseCodeGenerator;
Generating records within different date ranges for multiple groups is next challenge. I am sure this has been discussed before but finding threads is difficult. My first attempted query took almost 3 minutes to run and output is not correct.
 
Last edited:

mike60smart

Registered User.
Local time
Today, 11:07
Joined
Aug 6, 2017
Messages
1,899
Hi

As June7 has mentioned.

To achieve the Report that I did the only change I made was to include a Subform to record the Specific Days for the Course. This records each day as a Record vice trying to manage a string.
 

Ports

Registered User.
Local time
Today, 04:07
Joined
Jun 30, 2019
Messages
64
Hi

As June7 has mentioned.

To achieve the Report that I did the only change I made was to include a Subform to record the Specific Days for the Course. This records each day as a Record vice trying to manage a string.


What controls did you use in the subform to record specific days (eg. multiple comboboxes?)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:07
Joined
May 7, 2009
Messages
19,169
check query1 on your sample db.
check the result against your CourseCodeGenerator table.
 

Attachments

  • Generator - test_v1.zip
    84.2 KB · Views: 138

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:07
Joined
May 7, 2009
Messages
19,169
see the rptSessions for the list of dates and number of sessions.
 

Attachments

  • Generator - test_v1.zip
    98.1 KB · Views: 142
Last edited:

Ports

Registered User.
Local time
Today, 04:07
Joined
Jun 30, 2019
Messages
64
Thank you. Much appreciated. I am out now will have a look in the evening. Before I left home, I had a very quick look at the first file. I like the idea of generating all the dates straight away for all the courses. I have noticed that you created a query with system dates. Wouldn’t the OperatingSchedule table be enough as it contains all the dates of the academic year and indicates any holidays?

Thanks again. I’ll have lots to study!!!
 

Users who are viewing this thread

Top Bottom