Avatar of mishlce
mishlce
 asked on

Employee Shift Calculation in Access

I recently asked a question and it was answered successfully, however now I am adding to the originally asked question adding some additional scenarios.  Pls see Attachment.  
 Anna tab shows the data (table) I have in access.  The Results wanted tab shows what I want to display after my data is quried for Start of shift and End of shift.  There are multiple services logged during a shift but I just want the Start and End time of the shift and not all the detail in between.  AS I stated there are multiple scenarios.  Day shift, PM Shift Over night shift etc.  Pls see Attachment.  I think I chose the wrong Zoning originally.  I want to do this in Access Database as my table is stored in Access.
Anna.xls
Microsoft AccessSQL

Avatar of undefined
Last Comment
mishlce

8/22/2022 - Mon
Amgad_Consulting_Co

Hi,

is there any relation between Employees and shifts?, is there a table that says that employee X is working on shift type M ?
mishlce

ASKER
Unfortunatly no. This is a on demand service business and an employee can work many different types of shifts as they schedule their own services to meet customer needs.
peter57r

You will have to define  'long break' if you want to show multiple shifts for the same person.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
mishlce

ASKER
meaning a time (hrs) between shifts
peter57r

Not good enough. You have to be precise.

Do you mean at least 1 hour?
mishlce

ASKER
It is not consistent from day to day the amount of hours between an employees shift.  could be 6 up to 48 as they take days off.  They receive reqeusts for service, they call cust sched a svc time w customer and they log their start and end time for each service, but there is no consistancy in days off, or hours between shifts.  Could be 6 Hrs up to 48. So your saying I will not be able to get the results I am looking for.  
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
peter57r

As long as you are saying that the MINIMUM gap between shifts is 6 hours then you can handle the problem.  It doesn't matter if the actual gap is greater than 6 hours.

mishlce

ASKER
The below question is the question I posted originally  March 2nd, and was answered successfuly. This worked for the cross over midnight shift but didn't work for other scenarios.  How could I adjust this qry to work with all scenarios utilizing the 6 hours as gap time.

Question: I want the Max PM Start Time each day and the Max AM End Time the following day
This will then reach the shift worked starting with first acct worked start time and last account
worked end time. I want this for each day.  I am looking for qry to do this in access as my data is stored in an access table.

There are multiple start and end times of svc I just want to see first of shift and end of shift.

So it looks like this.  

Start Time:                       End Time:                    Hours Worked
11/8/10 10:10 PM             11/9/10 2:39 AM             4.23
11/9/10 7:52 PM               11/10/10 12:14 AM         4.22

MaxEndTime:
SELECT FORMAT(anna.[End Time],"Medium Date") AS EndTime,
MAX([End Time]) AS MaxEndTime
 FROM anna
   WHERE HOUR([End Time]) < 12
GROUP BY FORMAT(anna.[End Time],"Medium Date");

MinStartTime:
SELECT FORMAT(anna.[Start Time],"Medium Date") AS StartTime,
         MIN([Start Time])                         AS MinStartTime
    FROM anna
   WHERE HOUR([Start Time]) >= 12
GROUP BY FORMAT(anna.[Start Time],"Medium Date");

Now query these two queries.
SELECT t1.MinStartTime,
       t2.MaxEndTime
  FROM (MinStartTime AS t1
        INNER JOIN MaxEndTime AS t2
          ON DATEADD("d",1,t1.StartTime) = DATEVALUE(t2.EndTime));
ASKER CERTIFIED SOLUTION
peter57r

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mishlce

ASKER
Very Helpful
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck