query to calculate consecutive days worked

[jegupta]

I'm working on a database that a utility company will use to assign equip operators to jobs as they come up. I have a form where they enter the job start date, end date, etc. Then I have a couple of queries that run to tell them who is the next available backhoe operator(1st query looks for operators who are already booked during that time & the 2nd is an unmatched to find who is available). That part works well.

There is also a rule that an operator cannot work with any crew leader that is proving to be a bit more difficult for me. Below is a sample of my data. If I input 12/28 - 12/29 as a new job, Brady has worked 10 days, so is not able to take this job. If I change the new job to 12/29 -12/30, Brady should be eligible since he has now worked 0 consecutive days (break on 12/28).

[U][B]Oper_ID Operator  Job_Start_Date  Job_End_Date  Crew_Leader[/B][/U]
   3      Roth      12/26/2011     12/26/2011   Bell
   3      Roth      12/28/2011     12/28/2011   Bell
   8      Brady     12/18/2011     12/19/2011   Bell
   8      Brady     12/20/2011     12/25/2011   Bell
   8      Brady     12/26/2011     12/27/2011   Bell

For each new job date range, I need to figure out who would have hit the > 10 consecutive days (not worrying about weekends & holidays)with a given crew leader. Is there a way to calculate the number of consecutive days for each operator given a crew leader & new job date range> Is a query the best way to go, or would VBA work better?

Thanks,
Julie

[NeoPa]

It's actually quite hard to think of criteria for grouping that would handle consecutive days. As such, I imagine that VBA might be a more productive route.