Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Employee Shift Calculation in Access

Posted on 2011-03-04
10
Medium Priority
?
683 Views
Last Modified: 2013-11-29
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
0
Comment
Question by:mishlce
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 4

Expert Comment

by:Amgad_Consulting_Co
ID: 35036866
Hi,

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

Author Comment

by:mishlce
ID: 35036923
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.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35037088
You will have to define  'long break' if you want to show multiple shifts for the same person.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mishlce
ID: 35037323
meaning a time (hrs) between shifts
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35037380
Not good enough. You have to be precise.

Do you mean at least 1 hour?
0
 

Author Comment

by:mishlce
ID: 35037858
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.  
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35038004
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.

0
 

Author Comment

by:mishlce
ID: 35038070
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));
0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 35038843
Here is a sample solution  based on your data.

Open the file and look at the two tables- one is the raw data, and the other is an enpty tabe where the calculated shifts will go.

Close the tables.
Click the button on the form and then look at the results table.

The code that does all this is in module 6.
Database12.mdb
0
 

Author Closing Comment

by:mishlce
ID: 35057412
Very Helpful
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question