Calculate calendar days

[hzeigler4]

OK I am going to see if I can explain this. I have a table named Calendar0809. This table has a list of school dates and whether the date is an actual school date or not. I am attaching this table.

Then I have a table that lists students names, a start date, and an end date. A student may have multiple records in the table.

What I want to do is calculate the number of days between the start date and the end date; HOWEVER only count the days that are school days between the date range. Does this make since? I have attached my calendar so you can see what I am talking about.

 

[namliam]

Table naming Calander0809, suggests this is for school year 08-09 what will happen next year?

"DoyOWeek" is easy to calculate, no need to add that to the table: Weekday(yourdate,2)

"20DayPrd" is easy to do as well, resulting from Int(SchDay/20)+1

What is "ActSchDay" ?? Just a text of SchDay? Drop it too...

Now lets read your post/question...

You want to count the # of school dates between two dates.
Just select a count of your table where the SchDay idoesnt equal 0 (i.e. is an actual schoolday) and the CalenDate is "Between startdate and enddate"

I hope all that helps, good luck!

 

[hzeigler4]

Thank namliam but I do not need help creating the table Calendar0809. I already have something that recreates this every year. I do not quite understand what you are saying to do? How can I get whether it is a school day or not when the Calendar0809 table does not link to my students table?

I need to create a query that contains my students table and somehow looks up the date range from the students table in the calendar table and counts only the number of school days.

For example, student John smith has an action with a start date of 11/25/08 and an enddate of 12/1/08. The expression calculating the number of days should only return 2 days because 11/26, 11/27, and 11/28 are not school days.

 

[namliam]

This is what I said... Create a query to search Between startdate and enddate.

You can actually do it 2 ways:
1)
Query1: Select all actual school days.
Select *
from Calander0809 <== This will change every year?? Thus you have addapt all your queries etc every year??? Not a good idea !!!
Where schoolday <> 0

Query2:
Select name, count(*)
from students, calander
where calanderdate >= startdate
and calanderdate <= Enddate
group by name


Option 2:
Join the calander to your start and end dates, substract the schoolday fields.

 

[gemma-the-husky]

i think mailman is suggesting that for eaxh year, you have a table storing every date, together with a yesno flag to indicate whether its a schoolday or not

then you can easily count school days in a given range

this is probably easier than trying to store ranges of set holiday dates, and calculate based on that