Results 1 to 12 of 12

Thread: [RESOLVED] How to design this query for monthly report?

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2016
    Posts
    597

    Resolved [RESOLVED] How to design this query for monthly report?

    I have an Access table recording some Production data.

    There're two shifts:
    Day Shift (from morning 7am to 7pm)
    Night Shift (from evening 7pm to 7am)

    When cross month, for example, ID 9 is belong to Sep,2018. ID 71 is belong to Oct,2018.
    -------- ---------- -------------------------------
    ID Pieces dtDatetime
    8 559 9/30/2018 6:50:00 PM
    9 581 10/1/2018 6:50:00 AM
    10 524 10/1/2018 6:50:00 PM
    11 596 10/2/2018 6:50:00 AM
    10 395 10/2/2018 6:50:00 PM
    ...
    67 520 10/30/2018 6:50:00 AM
    68 224 10/30/2018 6:50:00 PM
    69 529 10/31/2018 6:50:00 AM
    70 224 10/31/2018 6:50:00 PM
    71 496 11/1/2018 6:50:00 AM
    72 395 11/1/2018 6:50:00 PM
    Code:
    Private Type DailyProduce
        lYear           As Long
        lMonth        As Long
        lDay         As Long
        lDayShiftVal       As Long    'number of pieces produced on Day Shift
        lNightShiftVal      As Long   'number of pieces produced  on Night Shift
    End Type
    
    Dim tDailyProduce() As DailyProduce
    How to design query to get result into a UDT array for a specific month?

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    Re: How to design this query for monthly report?

    Hi

    I would use a PIVOT query in Access to sort the Data by Month

    sample..

    Code:
    TRANSFORM Count(tbl_Service.SE_LfdNr) AS [Anzahl von SE]
    SELECT Format([SE_Datum],"yyyy") AS Jahr, Count(tbl_Service.SE_LfdNr) AS Retouren
    FROM tbl_Service
    GROUP BY Format([SE_Datum],"yyyy")
    PIVOT Format([SE_Datum],"mmm") In ("Jan","Feb","Mrz","Apr","Mai","Jun","Jul","Aug","Sep","Okt","Nov","Dez");
    regards
    Chris
    Last edited by ChrisE; Nov 20th, 2018 at 02:13 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3
    Addicted Member
    Join Date
    Jun 2018
    Posts
    189

    Re: How to design this query for monthly report?

    Hi,

    I don't have VB6 at the moment. However, I Just prepared this in Notepad. No idea this will work correctly, but you may get some idea.

    Code:
    Set rst = CreateObject("ADODB.Recordset")
    Set rst = <your recordset returned from a query> 
    'Eg:
    'for a single month: SELECT * FROM [Your_Table] WHERE (MONTH(dtDatetime) = 11)
    'for multiple months: SELECT * FROM [Your_Table] WHERE (MONTH(dtDatetime) IN (9, 10, 11))
    'You may also add GROUP BY and/or any sorting appropriately in your query
    'SELECT Pieces, dtDatetime FROM [Your_Table] WHERE (MONTH(dtDatetime) IN (9, 10, 11)) GROUP BY MONTH(dtDatetime), Pieces, dtDatetime ORDER BY dtDatetime DESC
    
    Dim tDailyProduce() As DailyProduce
    
    If rst.RecordCount > 0 Then
       ReDim tDailyProduce(0 To rst.RecordCount - 1) 'Set UDT array size              
    
       Dim counter as long      
    
       Dim shiftM As Date
       Dim shiftE As Date
       Dim dbTime As Date
       
       shiftM = TimeValue("07:00:00")
       shiftE = TimeValue("19:00:00")
    
       counter = 0
    
       Do Until rst.EOF 'Loop from first record to the last record in Recordset.
    
          tDailyProduce(counter).lYear = Year(rst(dtDatetime))
          tDailyProduce(counter).lMonth = Month(rst(dtDatetime))
          tDailyProduce(counter).lDay =  Day(rst(dtDatetime))
          
          dbtime = CDate(rst(dtDatetime))
    
          If (dbTime >= shiftM) And (dbTime <= shiftE) Then
    	tDailyProduce(counter).lDayShiftVal = CLng(rst(Pieces)) 
    	tDailyProduce(counter).lNightShiftVal = 0
          Else
    	tDailyProduce(counter).lNightShiftVal = CLng(rst(Pieces))  
    	tDailyProduce(counter).lDayShiftVal = 0	
          End if                                         
    
          rst.MoveNext  'Move to the next record of the Recordset.
    
          counter = counter + 1
       Loop
    
    End If
    Last edited by PGBSoft; Nov 20th, 2018 at 06:28 AM.

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: How to design this query for monthly report?

    It seems in your table you only have an "ending datetime", kind of like "my nightshift is reporting at 6:50 AM that 581 pcs have been produced".
    Why not just add a "starting datetime"-column for the shift?
    Kind of like: "I'm nightshift, and i start producing at 7:05 PM (hittingaBuzzer)" --> "I'm nightshift and ended at 6:54 AM and have produced 563 pcs (hittingaBuzzerAgain)"
    Then you could query by the starting datetime
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to design this query for monthly report?

    I'm not convinced it makes sense to divvy up Date values into 3 Long values, but whatever floats your boat. Not sure I'd bother with UDTs at all. I'd just keep the Recordset and use it until finished with it.

    What about a temporary table?

    Input data.txt:

    Code:
    ID,Pieces,dtDatetime
    8,559,9/30/2018 6:50:00 PM
    9,581,10/1/2018 6:50:00 AM
    10,524,10/1/2018 6:50:00 PM
    11,596,10/2/2018 6:50:00 AM
    10,395,10/2/2018 6:50:00 PM 
    67,520,10/30/2018 6:50:00 AM
    68,224,10/30/2018 6:50:00 PM
    69,529,10/31/2018 6:50:00 AM
    70,224,10/31/2018 6:50:00 PM
    71,496,11/1/2018 6:50:00 AM
    72,395,11/1/2018 6:50:00 PM
    Logic:

    Code:
        Dim Connection As ADODB.Connection
        Dim I As Long
    
        On Error Resume Next
        Kill "temp-data.txt"
        On Error GoTo 0
        Set Connection = New ADODB.Connection
        With Connection
            .Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
                & "Extended Properties='Text;CSVDelimited=True;Hdr=True';" _
                & "Data Source='.'"
            .Execute "SELECT " _
                   & "DateValue([dtDatetime]) AS [Date]," _
                   & "IIf(    TimeValue([dtDatetime]) >= #7:00:00 AM#" _
                   & "    And TimeValue([dtDatetime]) < #7:00:00 PM#," _
                   & "    [Pieces], 0) AS [DayShiftVal]," _
                   & "IIf(    TimeValue([dtDatetime]) >= #7:00:00 PM#" _
                   & "    Or TimeValue([dtDatetime]) < #7:00:00 AM#," _
                   & "    [Pieces], 0) AS [NightShiftVal] " _
                   & "INTO [temp-data.txt] FROM [data.txt]", _
                     , _
                     adCmdText Or adExecuteNoRecords
            With New ADODB.Recordset
                .CursorLocation = adUseClient
                .Open "SELECT " _
                    & "First([Date]) AS [Date]," _
                    & "Sum([DayShiftVal]) AS [DayShiftVal]," _
                    & "Sum([NightShiftVal]) AS [NightShiftVal] " _
                    & "FROM [temp-data.txt] GROUP BY [Date] ORDER BY [Date]", _
                      Connection, _
                      adOpenStatic, _
                      adLockReadOnly
                For I = 0 To .Fields.Count - 1
                    Debug.Print .Fields(I).Name;
                    If I < .Fields.Count - 1 Then
                        Debug.Print ", ";
                    Else
                        Debug.Print
                    End If
                Next
                Debug.Print .GetString(adClipString, , ", ", vbNewLine)
                .Close
            End With
            .Execute "DROP TABLE [temp-data.txt]", _
                     , _
                     adCmdText Or adExecuteNoRecords
            .Close
        End With
        On Error Resume Next
        Kill "schema.ini"
        On Error GoTo 0
    Results:

    Code:
    Date, DayShiftVal, NightShiftVal
    9/30/2018, 559, 0
    10/1/2018, 524, 581
    10/2/2018, 395, 596
    10/30/2018, 224, 520
    10/31/2018, 224, 529
    11/1/2018, 395, 496

  6. #6
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: How to design this query for monthly report?

    Why are you creating a problem where there is none
    both shifts start on the same day,so why not count their production as produced on that day
    (what you already seem to be doing judging by your Type DailyProduce)

    but if you want to hairsplit:
    just have the nightshift make 2 entrys(records) in the table on the last day of the month
    (1 for the last day and 1 for the first day)
    do not put off till tomorrow what you can put off forever

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: How to design this query for monthly report?

    Quote Originally Posted by IkkeEnGij View Post
    Why are you creating a problem where there is none
    both shifts start on the same day,so why not count their production as produced on that day
    (what you already seem to be doing judging by your Type DailyProduce)
    His Problem is, that he only has the "ending datetime" of the Shift. See my post about adding a column "starting datetime" for a shift.

    What i don't understand: Why does the nightshift count to the day prior, when 58% of the worktime belongs to the current day (5 hours for day prior, 7 hours for current day)?
    The only explanation (at least to me): The beginning of the shift determines to which day (resp. month) the produce belongs to.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8
    New Member
    Join Date
    Aug 2016
    Posts
    10

    Re: How to design this query for monthly report?

    Subtract seven hours from your shift times and you will get always (inside the OAD-Epoch) the correct shift date. An expression like
    Code:
    ..., DateValue(dtDatetime - #07:00:00#) AS ShiftDate, ...
    in your query should suffice.

  9. #9
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: How to design this query for monthly report?

    Quote Originally Posted by Zvoni View Post
    His Problem is, that he only has the "ending datetime" of the Shift. See my post about adding a column "starting datetime" for a shift.

    What i don't understand: Why does the nightshift count to the day prior, when 58% of the worktime belongs to the current day (5 hours for day prior, 7 hours for current day)?
    The only explanation (at least to me): The beginning of the shift determines to which day (resp. month) the produce belongs to.
    exactly, that is why s/he is creating a problem, where there is none
    it is as simple as pie:
    both shifts start at the same day
    so why not make that day the production day of both shifts ?
    and the table only needs 3 fields: Date (not datetime),DayProduction,NightProduction

    DaveDavis, is there any reason to not use such a simple system ?
    do not put off till tomorrow what you can put off forever

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2016
    Posts
    597

    Re: How to design this query for monthly report?

    Quote Originally Posted by IkkeEnGij View Post
    exactly, that is why s/he is creating a problem, where there is none
    it is as simple as pie:
    both shifts start at the same day
    so why not make that day the production day of both shifts ?
    and the table only needs 3 fields: Date (not datetime),DayProduction,NightProduction

    DaveDavis, is there any reason to not use such a simple system ?
    The data is coming from 3rd software, I can't control.

    @oumba has given a good idea by minus 7 hours.

    Thanks dilettante and PGBSoft for query design.

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: [RESOLVED] How to design this query for monthly report?

    In that case i'd rather check if the time is between midnight and noon, if yes then subtract 1 day, if no, do nothing, build query only on date (not time)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2016
    Posts
    597

    Re: [RESOLVED] How to design this query for monthly report?

    Quote Originally Posted by Zvoni View Post
    In that case i'd rather check if the time is between midnight and noon, if yes then subtract 1 day, if no, do nothing, build query only on date (not time)
    Correct.
    The crossed midnight is a common issue for reporting. Thank you for inputs.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width