Suggest a query: roll right up!!

[Vo0do0uk]

Have a table called tblshift with empid and tblshift as compound_PK

and have attributes at starttime and finish time.

so i can set an employee to work any date and any start and time within that date. i want to implement another table which i've called tblavailability and has the same attributes as tblshift this will record days the emp can't work! so here they will book days off work (for special occations etc )

my question is???

How can i write a query to ensure that if say: david gray's entery in the tblavailability was:

David Gray 05/05/2004 (starttime) (finishtime) -- any times!

the user wouldn't be able to then put david into the tblshift on the 05/05/2004??

can't think of way round it -- gagging for some guidance. have DB to post if needed but it's kinda large so would have to extract all seperate bits etc!!

cheers..

 

[dcx693]

The check that you want to use is to see if the time you are trying to schedule overlaps at all with the time that the user is not available.

In the tblavailability table, you'll want to search for the user name, the date, and the whether or not the times overlap. The hard part is how to tell if the times overlap. The only way there will be no overlap is if the start of the shift time is after the finishtime listed in tblavailability OR if the end of the shift time is before the starttime listed in tblavailability. (You should also run checks to see that the start time of the shift is before the end time of the shift when the user enters them.)

The easiest way to do this is with a Dlookup calculation. You'll have 3 criteria to search for: (1) name, (2) date, and (3) end of shift time < starttime in tblavailability OR start of shift time > end time in tblavailability. The Dlookup must return something other than 0 in order for the employee to be available for that shift. The syntax is doing to be tricky with all those criteria. You can use a custom function as an alternative. Let's see what other suggestions you get.