Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How do I keep resource schedule from overlapping? (2000)

    I am in the process of writing a scheduling application for a friend. One of his requirements is that the same resources (see resource definition below) can not be scheduled for more than one pick up or delivery at the same time.

    A resource is basically a "kit" made up of 1 or 2 drivers and a van.

    The schedule table has basically the following columns:

    DelId (Unique ID ~ Auto Increment)
    CustomerID
    BillingID
    PickUpDate
    PickUpTime
    PickUpDuration (in minutes)
    DestinationID
    DeliveryDate
    DeliveryTime
    DeliveryDuration
    ResourceID

    I know that on the Add PU/Del Form I need to check this table first to make sure that the new record is not overlapping either the PickUpDate/Time/Duration AND the DeliveryDate/Time/Duration for THAT ResourceID.

    Suppose I have the following record in the table already:
    DellId = 501
    CustId = 1234
    BillID = 1234
    PickUpDate = 20050525
    PickUpTime = 0900
    PickUpDuration = 65 (minutes)
    DestinationID = 5664
    DeliveryDate 20050526
    DeliveryTime = 1400
    DeliveryDuration = 45
    ResourceID = 55

    And I try to add the following record:
    DellId = 502
    CustId = 5587
    BillID = 5568
    PickUpDate = 20050525
    PickUpTime = 0930
    PickUpDuration = 20
    DestinationID = 5568
    DeliveryDate 20050527
    DeliveryTime = 1000
    DeliveryDuration = 30
    ResourceID = 55

    Ideally the add should fail because the Pickup date, time and duration for that resource (55) falls within the first records pickup window. The delivery is not an issue this time. But it is possible for the situation to be reversed where the PickUp is fine, but the devlivery falls w/n another scheduled delivery window or BOTH Pick up and destination could conflict with other pick ups or deliveries.

    See my dilemma?

    Any thoughts?

    Thanks in Advance!

    Craig

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: How do I keep resource schedule from overlapping? (2000)

    This is a common problem with resource scheduling applications, and it's very hard to solve with relational table designs. Some businesses solve it by creating records that represent chunks of time, and then only allow one use of that chunk. Hotels are a good example - they basically schedule on a day basis, but that doesn't always work well - some people do travel at night and sleep during the day.

    The more common approach to this problem is to use VBA logic to control the conflict resolution. We have employed an ActiveX control from http://www.dbi-tech.com/ called ctMDay that does scheduling functions. However it does require figuring out how the component works and then writing the VBA to integrate it with your application. There are also full scheduling applications available from them - see Schedule 7.0 for example. A number of other companies also offer scheduling components for Access - a search of the forum should find several threads on the topic.
    Wendell

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I keep resource schedule from overlapping? (2000)

    Craig

    Check out Clashing Events/Appointments for discussion and approach

    http://allenbrowne.com/appevent.html

    If your new to Access check out John Viescas new book Building Access Applications

    There are 4 production, good to go apps, some handle scheduling

    http://www.viescas.com/

    HTH, John

  4. #4
    New Lounger
    Join Date
    May 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I keep resource schedule from overlapping? (2000)

    John,

    That is exactly what I was looking for. Outstanding link!

    Thank you.Thank you.Thank you.Thank you.Thank you.Thank you.Thank you.Thank you.Thank you.Thank you.Thank you.Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •