Results 1 to 2 of 2
  1. #1
    zgadson is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2021

    Query Date Based on Offset Hours (5AM-4:49AM next day = 1 day)

    New to Access but I've managed to build a pretty complex database and form to retrieve information, however, I need to adjust my query to include offset hours from a specific date.

    Click image for larger version. 

Name:	Untitled.png 
Views:	12 
Size:	8.0 KB 
ID:	43834

    My 2nd shift employees work from 5PM through 2AM, so per the image above, rather than having 2 lines for every employee who worked into the next business day, I'd like to combine his two rows into one, starting with hour 0 into the next date. Problem is that I haven't one clue how to manage this.

    My Hour field is basically a count of actions per hour for every employee. Here's my query:

    SELECT DISTINCT Table_All_Data.Date, Max(Table_All_Data.Hour) AS MaxOfHour, Table_All_Data.HMTTYP, Max(Table_All_Data.HMPQTY) AS MaxOfHMPQTY, Table_All_Data.HMBLDG, Table_All_Data.USDESC, Sum(IIf([HOUR]=0,[HMQTY])) AS Hour0, Sum(IIf([HOUR]=1,[HMQTY])) AS Hour1, Sum(IIf([HOUR]=2,[HMQTY])) AS Hour2, Sum(IIf([HOUR]=3,[HMQTY])) AS Hour3, Sum(IIf([HOUR]=4,[HMQTY])) AS Hour4, Sum(IIf([HOUR]=5,[HMQTY])) AS Hour5, Sum(IIf([HOUR]=6,[HMQTY])) AS Hour6, Sum(IIf([HOUR]=7,[HMQTY])) AS Hour7, Sum(IIf([HOUR]=8,[HMQTY])) AS Hour8, Sum(IIf([HOUR]=9,[HMQTY])) AS Hour9, Sum(IIf([HOUR]=10,[HMQTY])) AS Hour10, Sum(IIf([HOUR]=11,[HMQTY])) AS Hour11, Sum(IIf([HOUR]=12,[HMQTY])) AS Hour12, Sum(IIf([HOUR]=13,[HMQTY])) AS Hour13, Sum(IIf([HOUR]=14,[HMQTY])) AS Hour14, Sum(IIf([HOUR]=15,[HMQTY])) AS Hour15, Sum(IIf([HOUR]=16,[HMQTY])) AS Hour16, Sum(IIf([HOUR]=17,[HMQTY])) AS Hour17, Sum(IIf([HOUR]=18,[HMQTY])) AS Hour18, Sum(IIf([HOUR]=19,[HMQTY])) AS Hour19, Sum(IIf([HOUR]=20,[HMQTY])) AS Hour20, Sum(IIf([HOUR]=21,[HMQTY])) AS Hour21, Sum(IIf([HOUR]=22,[HMQTY])) AS Hour22, Sum(IIf([HOUR]=23,[HMQTY])) AS Hour23, Format([DATE],"m/d/yy") AS Formated_Date, Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([USDESC],"WAREHOUSE SU",""),"WAREHOUSE T",""),"WAREHOUSE",""),"WAREHOUS",""),"WAREHOU",""),"WAREHO",""),"SUPERVISOR",""),"BLD 2",""),"-",""),"    ",""),"   ",""),"  ","") & " (" & LTrim([HMBLDG]) & ")" AS Formatted_User
    FROM Table_All_Data
    WHERE (((Table_All_Data.USDESC)=Forms![Productivity Search]!FromForm_Associate) And ((Table_All_Data.HMTTYP)=Forms![Productivity Search]!FromForm_Activity) And ((Table_All_Data.HMBLDG)=Forms![Productivity Search]!FromForm_Bldg)) Or (((Table_All_Data.USDESC)=Forms![Productivity Search]!FromForm_Associate) And ((Forms![Productivity Search]!FromForm_Activity) Is Null)) Or (((Table_All_Data.HMTTYP)=Forms![Productivity Search]!FromForm_Activity) And ((Forms![Productivity Search]!FromForm_Associate) Is Null)) Or (((Forms![Productivity Search]!FromForm_Activity) Is Null) And ((Forms![Productivity Search]!FromForm_Associate) Is Null))
    GROUP BY Table_All_Data.Date, Table_All_Data.HMTTYP, Table_All_Data.HMBLDG, Table_All_Data.USDESC, Format([DATE],"m/d/yy")
    HAVING (((Table_All_Data.Date) Between [Forms]![Productivity Search]![FromForm_StartDate] And [Forms]![Productivity Search]![FromForm_EndDate]))
    ORDER BY Table_All_Data.USDESC, Table_All_Data.DATE;
    Any guidance, advice, or suggestions would be greatly appreciated. I've searched, but unfortunately my particular request isn't a popular one.

    Thank you!

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Nevada, USA
    I might have a field in the table for "WorkDate" or something. We have a similar situation but normally our data is entered by that date, so the association is already there. In your case, a calculated field might work. I don't know your structure (and don't download from unknown locations), but something like:

    IIf(TimeValue(FieldName) < #5:00:00#, [Date] - 1, [Date])

    Date is a bad idea as a field name, as it can conflict with the built-in Date() function.

    Also, you may be able to accomplish this with a crosstab query instead of all the formulas.
    Paul (wino moderator)
    MS Access MVP 2007-2019

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Date Diff to calculate hours.
    By rywello in forum Queries
    Replies: 4
    Last Post: 05-30-2018, 02:13 PM
  2. Replies: 3
    Last Post: 06-23-2014, 11:51 AM
  3. Sum hours between anniv date and end date
    By rworthy in forum Queries
    Replies: 6
    Last Post: 06-07-2013, 06:34 PM
  4. Replies: 1
    Last Post: 08-08-2012, 01:42 PM
  5. Replies: 4
    Last Post: 08-06-2012, 10:25 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 - Senior Forums