I have no clue where to begin...

[wcboyd]

I am in the process of writing a scheduling application for a friend. One of his requirements is that the same resources can not be scheduled for more than one pick up 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
Duration (in minutes)
DestinationID
DeliveryDate
DeliveryTime
Duration
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.

Any thoughts?

Thanks in Advance!

 

[Bakta]

Looks like you have some writing to do on the Afterupdate on the Pickupdate + pickuptime event of your forms.

Since you picked the ressource up, it won't be available until it's delivered, so I'd only check the later (with the added time as necessary) when you add/edit a new form. Nothing needs to be done when deleting obviously.

HTH

ps : I am a relative newb in this, but having both date+time fields feels a bit un-normalized.

 

[wcboyd]

Bakta,

Thanks for the input, but I guess I may have been a bit unclear. The resource is not what is being picked up but who is picking up/delivering and with which vehicle.

I need to check before the record is added otherwise I could end up with a resource picking up an item on 5/24/05 @ 8:00AM for a duration of 60 minutes and then someone else or the same data entry person could schedule the same resource for pickup on 5/24/05 @ 8:15AM for a duration of 30 minutes on the other side of town.

Does that help clear up what I am trying to accomplish?

Thanks,

 

[Mile-O]

Why is there two fields called Duration? What is the need for either of these fields?

 

[wcboyd]

My fault; One is pick up duration and the other is delivery duration.
The purpose is to make sure that another pick up or delivery is not scheduled during that time.
So if ResourceID 001 has a pickup on 5/23/05 @ 8:00AM Duration of 45 minutes then the soonest this resource could be scheduled for another pick up or delivery would be 5/23/05 @ 8:46AM

Clear as mud?

 

[Bakta]

I think I got you right the first time . You want to make sure that once you create/edit a new form, you must check if RessourceID is "free" or not.

So, it means that you must check if said RessourceID is "free" during a new time frame.

Sounds like you have to, once you click on a button or whatever, add PU (date + hour + duration), transform the stuff into minutes and compare if there is a previous record filtered by this ressourceID with a result higher on any old DE (date+ hour+ duration). That should make sure your ressource is back.

Once that done, just apply the same method against any previous PU (date+ hour + duration) to make sure your ressource is still here.

Where PU = Pick Up time, and DE = Delivery time.

HTH