[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

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

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
mishlce
Asked:
mishlce
  • 4
  • 2
1 Solution
 
JAMcDoCommented:
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
 
mishlceAuthor Commented:
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
 
SharathData EngineerCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
JAMcDoCommented:
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
 
mishlceAuthor Commented:
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
 
mishlceAuthor Commented:
I have re submitted the question pls see 03/04/11 07:00 AM, ID: 26864074 :  Employee Shift Calculation
0
 
mishlceAuthor Commented:
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now