Turnaround/Lag Times using School Calendar

[lspelman]

Hi there



I have been spending alot of time searching for a way to do this and can't figure it out.

I need to be able to show the average turnaround times from the time an assessment was received to completed. I can figure that part our ok but when I want the actual average . . . it is based on the operational days in a month of a school calendar which will change from year to year.

I'm not sure how to set this up. If you need more information just let me know.

Cheers

[CraigDolphin]

A database cannot tell you something you haven't already told it first. It can do a lot of math on that information, but it has to have the informational building blocks already in place.

In your case, you have a start and end date. So far, so good: access can work with calendar dates. It can even tell you which day of the week it is if you give it a date.

But how is the database to know what your school district considers an 'operational date'?

This is where you need to be able to specify precisely how the database can figure out whether a given date is an operational date.

If it is something simple like 'it has to be monday to friday, but not sat/sun' then that's very achievable using code to make a function that can answer whether a given date is operational or not.

If it requires more information, such as 'for calendar year 2012 it has to be between Aug 1 and December 5 AND be mon-friday' then it's likely you're going to need to provide access with a lookup table that lists a beginning and ending date for each calendar year that you are likely to encounter.

You'll also need to consider what the answer should be if the required input information is not present in that table.

In this situation, you might want to use vba to generate a temporary table/query that lists all dates between the assesment submission date and the completion date, and then assigns each date with a status of 'operational' or not using the logic you've supplied, and the supplementary information that you've provided in tables. Then use a domain aggregate function to count all 'operational' dates in that temporary table/query, and return the number of operational days when required.

Depending on how your district defines operational days, this could be quite complex, or quite simple.

[lspelman]

Would it work best if I set up a table with the 12 months and the operational days for those months?

Field: ID
Field: MonthName
Field: OpDays

Then the following year add an additional 12 records with the new operational days?

Please bear with me . . .it has been a long day trying to understand this.

How would I then tell the database to use the correct month/year with op days based on the assessment month/year. If the Assessment was done in August I want to use the OpDays from the month of August in the OperationalDays Table. I've been playing with queries but haven't been able to figure it out.

I think once I can that sorted out then I can calculate the turnaround days based on Referral Date and Assessment Date and use that result with correct operational days of that month to figure out the average turnaround time.

Is this sounding like its going to work?

[CraigDolphin]

If you cannot use objective logic to determine whether a given date is 'operational' or not, then you would likely have to create a table that lists every day of every year on a separate row, with a yes/no field indicating whether that date was operational.
ie.
table OperaitonalDates
DateID (auto, pk)
ThisDate (Datetime)
Operational (yes/no)

Or, you could use a table that lists operational date ranges
table OperationalRanges
OperationalRangeID (auto,pk)
StartOperationalDate (datetime)
EndOperationalDate (datetime)

In the first case, you could use Dlookup to find out the operational status of any given date in a query. In the second case, you would use Dcount to count the number of operational ranges where the startdate is below or equal to the date of interest, and where the end date is equal to or higher than the date of interest. If the count is 0 then the date was not operational. If the count is higher than zero, then the date was operational.

Then you could a criterion query to count the number of operational dates in the range of interest. I'll see if I can come up with an example db later on to show you what I mean.

[lspelman]

Thanks for your help.

I don't need to know which days are operational just the total instructional days for each month within the year which I have been given.

I created a table using that information and just came across the DLookup this morning which I am going to try and use. Not quite sure how I will get it to find the corresponding month/year but will play around with it. The Referral and Assessment Dates are entered by dd/mm/yyyy and I need them to lookup to the mm/yyyy in the OperationalDays Table to get the correct number of instructional days.

If you have any help/tips on how to use it that would be great.

[CraigDolphin]

Well, maybe this link will give you some access to some of the tools available to you:
http://www.techonthenet.com/access/functions/index.php