[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.
Quote:
-------- ---------- -------------------------------
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?
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
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
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
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
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)
Re: How to design this query for monthly report?
Quote:
Originally Posted by
IkkeEnGij
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.
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.
Re: How to design this query for monthly report?
Quote:
Originally Posted by
Zvoni
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 ?
Re: How to design this query for monthly report?
Quote:
Originally Posted by
IkkeEnGij
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.
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)
Re: [RESOLVED] How to design this query for monthly report?
Quote:
Originally Posted by
Zvoni
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.
