By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,802 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

query to calculate consecutive days worked

P: 1
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).

Expand|Select|Wrap|Line Numbers
  1. Oper_ID Operator  Job_Start_Date  Job_End_Date  Crew_Leader
  2.    3      Roth      12/26/2011     12/26/2011   Bell
  3.    3      Roth      12/28/2011     12/28/2011   Bell
  4.    8      Brady     12/18/2011     12/19/2011   Bell
  5.    8      Brady     12/20/2011     12/25/2011   Bell
  6.    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
Dec 20 '11 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,273
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.
Dec 21 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.