Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    rywello is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    May 2018
    Posts
    14

    Shift Differentials

    I am having issues finding the calculations for shift differentials. I have tried different modules to try and pull shift differential hours from different shifts.

    For example:
    I am trying to pull the hours from the night shift where the time is greater than 10pm and less than 6am and the afternoon shift where the shift begins 11am or later and ends before 10pm.



    I have attached a copy of my database where I am trying to run query TheTimeQuery and use the functions from the modules I created but nothing seems to be working. Any help would be appreciated.

    Thanks!
    Attached Files Attached Files

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have a look now, I modified the day_hours function and calculated the night_hours=TotalHrs-DayHrs.

    Cheers,
    Vlad
    Attached Files Attached Files

  3. #3
    rywello is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    May 2018
    Posts
    14
    That works great! I have added a part to capture the afternoon hours. Can you help me modify that? Thanks!
    Attached Files Attached Files

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Not really following you, why the afternoon ours? What about the 8 AM to 5 PM shift? Are you going to split it in 2 hours of day hours (8 AM to 11 AM) and 6 afternoon hours? Can't you use your shift type to determine the type of day hours (regular vs afternoon)?

    Cheers,
    Vlad

  5. #5
    rywello is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    May 2018
    Posts
    14
    There is a shift differential on the afternoon/evening shift that starts 11am or later and ends before 10pm. So i need to capture those hours as well.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here you are, I just use the shift code (JobNumber) to separate the afternoon hours.

    Cheers,
    Vlad
    Attached Files Attached Files

  7. #7
    rywello is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    May 2018
    Posts
    14
    Thanks! That works well but it doesn't work for the other shift types:

    N T10 - 10:30PM - 7:00AM
    PM 14 - 2PM-10PM

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Just add them to the expressions. Not sure I get the first one, isn't that a night shift?

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here is your updated query, I used InStr to look for "PM" in the shift code (JobNumber):

    Code:
    SELECT EmployeeLog.Day, EmployeeLog.Name, EmployeeLog.JobNumber, EmployeeLog.TimeIn, EmployeeLog.TimeOut, EmployeeLog.Lunch, (Abs(DateDiff("n",[TimeIn],[TimeOut])/60)-[Lunch]) AS Total, IIf(InStr([JobNumber],"PM")=0,day_hours([TimeIn],[TimeOut])-[Lunch],0) AS [Day Hours], [Total]-([Day Hours]+[Afternoon Hours]) AS [Night Hours], EmployeeLog.Schedule_id, IIf(InStr([JobNumber],"PM")>0,day_hours([TimeIn],[TimeOut])-[Lunch],0) AS [Afternoon Hours]
    FROM EmployeeLog;

  10. #10
    rywello is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    May 2018
    Posts
    14
    Thanks so much for your help! It works great! Have a nice weekend!

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're welcome, you too!

  12. #12
    rywello is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    May 2018
    Posts
    14
    Hey Vlad, When the calculations are done for the 10:30PM - 7:00AM shift the hours total out to 15. Can you assist with that? Not sure why that is happening. The different shift types are listed in the tblshifttype table. Thanks!

  13. #13
    rywello is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    May 2018
    Posts
    14
    Also, when I add the below it does not calculate properly anymore.

    Afternoon Hours: IIf([JobNumber]="MF-PM" Or "PM 14",day_hours([TimeIn],[TimeOut])-[Break],0)

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The last expression doesn't work, you need to use instr to look for PM or use IN: [JobNumber]IN ("MF-PM","PM 14"). Anyway, have a look at the attached sample, I have modified your shifttype table and I think is a much better solution as you don't need any time calculations. Look at the new query TheTimeQuery_No Calculations.

    Cheers,
    Vlad
    Attached Files Attached Files

  15. #15
    rywello is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    May 2018
    Posts
    14
    That works perfect. Way easier for sure. Thanks for your help!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. disable shift key
    By AzizSader in forum Forms
    Replies: 3
    Last Post: 09-20-2014, 09:02 AM
  2. shift + f2
    By willifords in forum Access
    Replies: 1
    Last Post: 07-10-2014, 05:17 PM
  3. Alt + Shift + Tab -- No Icon!
    By LindaRuble in forum Programming
    Replies: 7
    Last Post: 03-18-2013, 03:15 PM
  4. Shift By Pass
    By pkstormy in forum Code Repository
    Replies: 4
    Last Post: 03-18-2012, 05:51 PM
  5. Help with Shift+F2 needed
    By Rackrunner in forum Access
    Replies: 1
    Last Post: 05-13-2011, 05:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums