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
260 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

707 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

17 Experts available now in Live!

Get 1:1 Help Now