Calculation for a range of times

[Valdrin]

Hi all,

I have a database for scheduling panelist at a multi-day conference that consists of 3 tables (Panelists, Events and a Join table)

Each panelist lists the earliest and latest panels that they can attend on the various days of the conference using 6 fields (FridayEarliest, FridayLatest, SaturdayEarliest, SaturdayLatest, SundayEarliest, SundayLatest).

I want to use a calculation field to give a warning when a panelist is placed on a panel that is not within the range of his acceptable times, but I am having difficulty coming up with a calculation that will work.

Any and all help will be greatly appreciated.

[comment]

I would use a related table to keep the panelists' availability data, with fields:

• PanelistID

• Date

• FromTime

• ToTime

If you add a calculation field to the join table =

Events::Date

you can define a relationship from the join table to a second occurence of the Availability table as:

Join::PanelistID = Availability 2::PanelistID

AND

Join::cEventDate = Availability 2::Date

so that when you create a join record, it can "look" at the related record in Availability 2, and do the calc.

You could even take it further by pulling the event times into the join table too, and defining the relationship to Availability 2 as a range - so that the mere existence of a related record indicates availability.

[Valdrin]

Thanks, I will give that a try -- but I am still a little shaky on exactly how to do the calculation.

[comment]

Something like:

Events::StartTime ≥ Availability 2::FromTime

and

Events::EndTime ≤ Availability 2::ToTime

The result is 1 (true) if the panelist is available, 0 (false) otherwise.