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
284 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
hit enter key to run macro 13 24
aggregate query? 20 53
Part 2 to aggregate query solved qtn 12 42
Switch 5 18
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

822 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