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
272 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

929 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now