CODEWITHSUNDEEP
ms-accessschedule
Chris Jones
Chris Jones

Reputation: 672

Bringing in Schedules for Access calculations

I really don't know how to ask this question or title it but here I go. I work in a school system and I have created a database for some psychologists to use to track their referrals. By state rules,they have 60 days from the date of their first meeting to finish the process. Weekends still count but HOLIDAYS DO NOT. I do not really know how to use the calender we have so that we have an accurate Calculation. For instance, with Holidays accounted for, if a kid was started today, he would need to have everything finished on 1/18/2013 That is 60 days from now based on our schedule. Does anyone have any idea where I should start?

Edit

Ok, so I now have a Calender table. Here is my issue. I have my column that I used to indicate which days are used in calculating my 60 days. Weekends can be used in that calculation. HOWEVER, they cannot be used in the result. If the 60th day lies on a Sunday or Saturday, then the date would need to go to the Friday before. I guess my first issue is really, how do I limit my calculation to the dates in my calender table?

Upvotes: 2

Views: 127

Answers (2)

Fionnuala
Fionnuala

Reputation: 91376

You can check the weekday to ensure you have not chosen a weekend:

SELECT TOP 1 CalDate, WDay 
FROM (SELECT Top 60 c.CalDate,Weekday([Caldate]) AS WDay
      FROM Calendar c
      WHERE c.Holiday=False) a
WHERE WDay Not In (1,7)
ORDER BY CalDate DESC

Upvotes: 0

HansUp
HansUp

Reputation: 97131

This can be easy with a calendar table.

PARAMETERS start_date DateTime;
SELECT TOP 1 sub.the_date
FROM
    (
        SELECT TOP 60 the_date
        FROM tblCalendar
        WHERE
                the_date>=[start_date]
            AND work_day=True
        ORDER BY the_date
    ) AS sub
ORDER BY sub.the_date DESC;

That query is based on the assumption you have set work_day to True for the dates you want evaluated. IOW, work_day will be False only for your organization's holidays.

For sample code to create and load your calendar table, see the CreateTable_calendar() and LoadCalendar() procedures at Using Start Date and End date in Access query. To initially assign all dates including weekend days as work days, make this change in LoadCalendar().

'rs!work_day = Not (Weekday(dte) = vbSunday Or _
'    Weekday(dte) = vbSaturday)
rs!work_day = True

Finally, manually edit the table to change work_day to False for your holidays.

Upvotes: 1

Related Questions