Volunteer Scheduling

[Dan M]

I'm looking to schedule events at my college with student volunteers. At the
moment I have a table "Availability" with fields:

StudentID
10:30
11:30
12:30
etc

where the StudentID is a combo box that selects from a table containing the
student's contact info, and the times are yes/no check boxes. This does not
seem like the most efficient way to do this. Unfortunately, I can't just
have a start and end time of their availability, as they are students, and
are available at multiple times during the day, with classes in between.

In another table, "Events" in which I plan the actual events, there are
fields "StartTime" and "EndTime" which, coincidentally enough, hold the
information for the starting and ending time of the event, and "StudentID"
which once again selects from the "StudentInfo" table.

Ideally, in the form that populates the event table, I would like to filter
out only the students who are available during the event to be shown, either
in a sub form, or in the Combo Box that selects their StudentID and
associates them with that event.

Am I asking too much?

 

[Fred]

First a few questions (along with temporarily presumend answers)

Do your availability periods always start on XX:30 times? Yes
Are availability periods always in 1 hour periods? (e.g. a 1 hour period, a
2 hour period) Yes
Do your events always start on the XX:30 times? Yes
Are event lengths always in 1 hour increments? Yes

There are a lot of ways to do this, all should involve normalizaiton of your
availability table, which will mean splitting it into instances of
availability, one record per instance.

One way (that would support your objective) would be a table with
StudentID, Date, Start Time, and length of the availability in 1 hour
increments.

Make an event table with the last three fields being the same as the
availability table.

Format/limit your entries based on the answers to the 4 questions above.
Then it's a simple matter of matches (on those last 3 fields) in the
availability table to those 3 fields in your event table.

 

[CraigH]

Hi,
As you said with the presumed answers there is still one flaw.
If a student has Event has
date 1/30/09 1/30/09
Time 12:30 12:30
duration 3 2

They will not match up so Date and time can be equal in the query (I assume)
but you will want Event Length <= Student Length.

Of course you will now have to decide if it is better to have this student
do the 3 1 hour events or only this 1 2 hour event and give them a rest... or
is s/he already in the other 2 hour event that -- Woops found another problem

What if the time available for the student is 11:30 with 3 hours still could
do the event.

Ok that's it for me right now. Will need to think more on this before I go
on.

Craig

 

[CraigH]

Ok - Quick and Dirty

(((StudentAvailability.StartTime)<=[Events].[Starttime]) AND
((StudentAvailability.Duration)>=[Events].[Duration]+DateDiff("h",[StudentAvailability]![StartTime],[Events]![StartTime])));

The date is equal

Craig