Solved

How do I calculate multiple services to read on one line item in access as a shift crossing over midnight

Posted on 2011-03-02
7
291 Views
Last Modified: 2012-05-11
I have multiple service start and stop times crossing over from one day to the next as they are 3rd shifts.  I want to qry this data in access to read as below.

I have attached the data I have in an access table. The employee clocks in and out for each svc provided to each location.  I want it to show in one line item per employee per shift as I have  listed below and I have also placed in the spreadsheet.  Many times this is a cross over midnight into the following day.  Along with this it is an ending of a shift in the AM of one day and the start of a shift in the same day but in the PM Hr.  this is what I am trying to accomplish in Access database: Only thing I can think is Min Max times per AM per PM????? Pls help

Employee       St Dt      End DT      Start Time        End Time               Hrs Wrkd
Joe      20101108      20101109      10:10:19 PM        2:39:31 AM                  4.23
Katie      20101108      20101109      7:05:24 PM        12:10:30 AM           4.91
Joe      20101109      20101110      8:14:19 PM        1:36:06 AM                  5.22



Svc-Times-Over-Midnight.xls
0
Comment
Question by:mishlce
  • 4
  • 2
7 Comments
 
LVL 3

Expert Comment

by:JAMcDo
ID: 35024140
Try this query as represented by this SQL statement:

SELECT TblEmployeeService.Employee, TblEmployeeService.StDt, TblEmployeeService.EndDt, TblEmployeeService.[Start Time], TblEmployeeService.[End Time], ([End Time]-[Start Time])*24+IIf(([End Time]-[Start Time])<0,24,0) AS [Hrd Wrkd]
FROM TblEmployeeService;

To create the query, create a blank query in SQL view and paste the SELECT  . . . statement (including the semi-colon) into the query.  Make any adjustments for table/field names as matches your table/fields and then go to Design view.

It worked OKay for me with your data.  I also tried same day start and end times and it was OKay.

It will not work correctly if the start and end times span more than 23:59:59 hours.

Hope this helps,

J.

0
 

Author Comment

by:mishlce
ID: 35027766
This worked for each line item and calcustated the hours worked per line item.  Thank You.  However I want to go one step further and show the shifts or total Hours worked by each employee.  I have attached the spreadsheet with results I got from running the above qry.  I also placed in here what I want the end result to look like. The overnight shift always starts with a PM shift and Ends with an AM shift the following day.  Then starts that same day they ended with AM and then they start the shift later that evening with a PM  Start Time and so on.  They have many start and end times throughout their shift, but I want to see only Start of shift and end of shift with hours calculated.  Pls see attached spreadsheet.

Employee       St Dt            End DT             Start Time              End Time                     Hrs Wrkd
Joe             20101108      20101109           10:10:19 PM         2:39:31 AM                  4.23
Joe             20101109      20101110             8:14:19 PM         1:36:06 AM                  5.22
Katie           20101108      20101109            7:05:24 PM        12:10:30 AM                  4.91


TblEmployeeService.xls
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35032243
I think this is a duplicate of another question. http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_26853826.html?cid=1572#a35032013
If so, please close one question.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 3

Expert Comment

by:JAMcDo
ID: 35033314
I think I may have an answer.  It is not pretty in that involves 4 queries and 3 are nested in successive queries.  But it seems to work.

The answer/result in in the QSelEmplSrvShiftDatesTotals query.

See the attached MS Access File.

I had a heck of a time with the dates and times.  They did not seem to behave as Date/Time until I refreshed them in the DB table.  I hope this was only a conversion thing between Access > Excel > Access.

Give it a try,

J.



 Svc-Times-Over-Midnight.accdb
0
 

Accepted Solution

by:
mishlce earned 0 total points
ID: 35036897
I tried to open the attc, however it gave me and "unrecognizable database error"  

I have re submitted my question with updates and additions.  Pls see new question titled Employee Shift Calculation.

03/04/11 07:00 AM, ID: 26864074  Employee Shift Calculation:  
0
 

Author Comment

by:mishlce
ID: 35037057
I have re submitted the question pls see 03/04/11 07:00 AM, ID: 26864074 :  Employee Shift Calculation
0
 

Author Closing Comment

by:mishlce
ID: 35081155
My first question was answered, I was unable to open the attached file for the answers to my next questions.  I have closed this and Re Submitted my quesiton with more data and detail.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

830 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