[RESOLVED] Inserting and Processing Data from one table to another table
Im trying to process the data from tblTempData to tblPData using the ff sql
Code:
INSERT INTO tblPData ( fldMacID, fldYear, fldMonth, fldDay,fldTimeIn1,fldTimeOut1,fldTimeIn2,fldTimeOut2 )
SELECT
T1.fldMachineId, Year( T1.theDate),Month( T1.theDate),Day( T1.theDate),
T2.fldLogDate - int(T2.fldLogDate) as TimeIn1,
T3.fldLogDate - int(T3.fldLogDate) as TimeOut1,
T4.fldLogDate - int(T4.fldLogDate) as TimeIn2,
T5.fldLogDate - int(T5.fldLogDate) as TimeOut2
FROM (((
(Select Distinct tblTempData.fldMachineId,int(tblTempData.fldLogDate) as theDate from tblTempData) as T1
LEFT JOIN tblTempData as T2 ON (T1.fldMachineId = T2.fldMachineId AND T1.theDate = Int(T2.fldLogDate) AND T2.LogStatus = 1 AND Hour(T2.fldTimeLog) < 11) )
LEFT JOIN tblTempData as T3 ON (T1.fldMachineId = T3.fldMachineId AND T1.theDate = Int(T3.fldLogDate) AND T3.LogStatus = 2 AND Hour(T3.fldTimeLog) < 15) )
LEFT JOIN tblTempData as T4 ON (T1.fldMachineId = T4.fldMachineId AND T1.theDate = Int(T4.fldLogDate) AND T4.LogStatus = 1 AND Hour(T4.fldTimeLog) >= 11) )
LEFT JOIN tblTempData as T5 ON (T1.fldMachineId = T5.fldMachineId AND T1.theDate = Int(T5.fldLogDate) AND T5.LogStatus = 2 AND Hour(T5.fldTimeLog) >= 15)
When i run that code in MS Access Querry Tools it didnt work as expected. And I cant really get it right.
I really appreciate any help on this.
Please see image for more info on my table and fields
Thank you very much.
Last edited by lance1578; Jan 6th, 2018 at 10:13 AM.
Reason: correction on code
Re: Inserting and Processing Data from one table to another table
@jmcilhinney
Ohh, Thank you very much. Sorry for the wrong post. I've been happy to be moved my post in Database Development. I really need help on this.
Re: Inserting and Processing Data from one table to another table
Hi Lance,
I saw the other thread, did you get the sorted Data to a Datagridview ??
if so you can use this to export the Datagridview Data to a Table..
Code:
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
DataGridViewDetails()
End Sub
Sub DataGridViewDetails()
Dim Sqlc As String
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=D:\NWind.mdb")
Sqlc = "insert into tbl_Jahr (JA_Datum, JA_KW, JA_Tag,JA_TagNr,JA_TagKe) values (@f1, @f2, @f3, @f4, @f5)"
Dim dp As New OleDbDataAdapter
Dim p1, p2, p3, p4, p5 As OleDbParameter
con.Open()
dp.InsertCommand = New OleDbCommand(Sqlc, con)
p1 = dp.InsertCommand.Parameters.Add("@f1", OleDbType.Date)
p2 = dp.InsertCommand.Parameters.Add("@f2", OleDbType.Numeric)
p3 = dp.InsertCommand.Parameters.Add("@f3", OleDbType.VarChar, 50)
p4 = dp.InsertCommand.Parameters.Add("@f4", OleDbType.Numeric)
p5 = dp.InsertCommand.Parameters.Add("@f5", OleDbType.VarChar, 50)
With DataGridView1
For i As Integer = 0 To .Rows.Count - 1
With .Rows(i)
p1.Value = .Cells(0).Value
p2.Value = .Cells(1).Value
p3.Value = .Cells(2).Value
p4.Value = .Cells(3).Value
p5.Value = .Cells(4).Value
dp.InsertCommand.ExecuteNonQuery()
End With
Next
End With
con.Close()
End Sub
regards
Chris
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.
Re: Inserting and Processing Data from one table to another table
@ChrisE
Thank you very much for your time. Im not in the Datagrid view for now. I just want to process the data via SQL query. My old program didnt work well now in windows 10. So i decided to reencode it from vb6 to vb.net for more ease of use. In my old code I only use sql query to process the raw data. If you happen to see my other thread I was asking on how to properly place Raw Data in proper column. @si_the_geek gave me a perfect line of code to do it however im having problem to put it into action :-) If I run the code it keeps asking me for paramaters. The way i understand the data is all in tblTempData so I know Im missing something in the code. And i cant figure it out. I like @si_the_geek code, its short and simple compare to my previous long query :-) I really appreciate your time. I will put your suggestion in my codebank, I might it use later.
Re: Inserting and Processing Data from one table to another table
I can manage to get the expected result of TimeIn1,TimeOut1,TimeIn2,TimeOut2 individually in the following sql
Code:
//TimeIn1
SELECT tblTempData.fldMachineId, Year(tblTempData.fldLogDate) AS [Year], Month(tblTempData.fldLogDate) AS [Month], Day(tblTempData.fldLogDate) AS [Day], Format([tblTempData].[fldLogDate],"hh:mm") AS TimeIn1
FROM tblTempData
where Format([tblTempData].[fldLogDate],"hh:mm") < "11:00"
ORDER BY tblTempData.fldMachineId,Day(tblTempData.fldLogDate);
//TimeOut1
SELECT tblTempData.fldMachineId, Year(tblTempData.fldLogDate) AS [Year], Month(tblTempData.fldLogDate) AS [Month], Day(tblTempData.fldLogDate) AS [Day], Format([tblTempData].[fldLogDate],"hh:mm") AS TimeOut1
FROM tblTempData
where Format([tblTempData].[fldLogDate],"hh:mm") >"10:00" and Format([tblTempData].[fldLogDate],"hh:mm") < "14:00"
ORDER BY tblTempData.fldMachineId,Day(tblTempData.fldLogDate);
//TimeIn2
SELECT tblTempData.fldMachineId, Year(tblTempData.fldLogDate) AS [Year], Month(tblTempData.fldLogDate) AS [Month], Day(tblTempData.fldLogDate) AS [Day], Format([tblTempData].[fldLogDate],"hh:mm") AS TimeOut1
FROM tblTempData
where Format([tblTempData].[fldLogDate],"hh:mm") >"11:30" and Format([tblTempData].[fldLogDate],"hh:mm") < "14:00"
ORDER BY tblTempData.fldMachineId,Day(tblTempData.fldLogDate);
//TimeOut2
SELECT tblTempData.fldMachineId, Year(tblTempData.fldLogDate) AS [Year], Month(tblTempData.fldLogDate) AS [Month], Day(tblTempData.fldLogDate) AS [Day], Format([tblTempData].[fldLogDate],"hh:mm") AS TimeOut1
FROM tblTempData
where Format([tblTempData].[fldLogDate],"hh:mm") >"16:00"
ORDER BY tblTempData.fldMachineId,Day(tblTempData.fldLogDate);
What I deed in my last vb6 program is to create a separate 4 tables(tblTimeIn1,tblTimeOut1,tblTimeIn2,tblTimeOut2) then insert each data using code above. I have another table(tblGetAllLogs) to merge the 4 tables.
Im trying to use @si_the_geek suggested code "select distinct" to lessen the number of tables but i can get it right till now. :-)
Re: Inserting and Processing Data from one table to another table
Hi Lance,
I was looking at you Image how the Data comes in.
It does'nt really make sence the LogStatus is 1,2 - 1,2 etc..
I think it will be easier if it were 1,2,3,4 = for each TimeIn/out
try and Update the Database Table first
something like..
Code:
UPDATE tblLog SET tblLog.LogStatus = 3
WHERE (((Format([fldLogDate],"hh:mm")) Between #12/30/1899 10:00:00# And #12/30/1899 13:05:00#));
I updated to LogStatus 3 for times between 10:00:00 and 13:05:00
well I'm in germany so the Update query might look strange with the Times.
do the same for Logstatus 4 (TimeOut2)
then you have a LogStatus 1-4 rather than 1-2-1-2 for 4 Columns
when you have the LogStatus in place it really is just telling the Datagridview
place the Time for Logstatus 1 in Cell = ?
Time LogStatus 2 in Cell = ?
etc...
regards
Chris
Last edited by ChrisE; Jan 7th, 2018 at 06:18 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.
Re: Inserting and Processing Data from one table to another table
@ChrisE
The data comes from the biometric machine with the following format:
2 2017-7-20 7:05:00 1 0
2 2017-7-20 11:25:00 1 1
2 2017-7-20 12:30:00 1 0
2 2017-7-20 17:45:00 1 1
.....
almost 4k lines per month
LogStatus (TimeIn) = 1 (whether it is TimeIN1 or TimeIN2) once the user login it will mark 1
LogStatus (TimeOut) = 2 (whether it is TimeOUT1 or TimeOUT2) once the user logOut it will mark 2
Sometimes the user forgot to login or logout so the field is blank.
Using this code ill be able to collect all TimeIn and TimeOut.. Sadly it takes me 4 Tables
Code:
// Insert to tblTimeIN1
INSERT INTO tblTimeIN1 ( fldMacID, fldYear, fldMonth, fldDay, fldTimeIn1 )
SELECT tblTempData.fldMachineId, tblTempData.fldLogDate, tblTempData.fldLogDate,tblTempData.fldLogDate, tblTempData.fldLogDate
FROM tblTempData
WHERE (((Format([tblTempData].[fldLogDate],"Short Time"))<"11:00")and tbltempdata.fldLogStatus = 1))
ORDER BY tblTempData.fldMachineId, Day(tblTempData.fldLogDate);
// Insert to tblTimeOut1
INSERT INTO tblTimeOut1 ( fldMacID, fldYear, fldMonth, fldDay, fldTimeOut1, fldLogStatus )
SELECT tblTempData.fldMachineId, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogStatus
FROM tblTempData
WHERE Format([tblTempData].[fldLogDate],"hh:mm") >"11:00" and Format([tblTempData].[fldLogDate],"hh:mm") < "14:00" and tbltempdata.fldLogStatus = 2
ORDER BY tblTempData.fldMachineId, Day(tblTempData.fldLogDate);
// Insert to tblTimeIN2
INSERT INTO tblTimeIN2 ( fldMacID, fldYear, fldMonth, fldDay, fldTimeIn2, fldLogStatus )
SELECT tblTempData.fldMachineId, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogStatus
FROM tblTempData
WHERE Format([tblTempData].[fldLogDate],"hh:mm") >"11:30" and Format([tblTempData].[fldLogDate],"hh:mm") < "14:00" and tbltempdata.fldLogStatus = 1
ORDER BY tblTempData.fldMachineId, Day(tblTempData.fldLogDate);
// Insert to tblTimeOut2
INSERT INTO tblTimeOut2 ( fldMacID, fldYear, fldMonth, fldDay, fldTimeOut2, fldLogStatus )
SELECT tblTempData.fldMachineId, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogStatus
FROM tblTempData
WHERE (((Format([tblTempData].[fldLogDate],"Short Time"))>"16:00" and tbltempdata.fldLogStatus = 2))
ORDER BY tblTempData.fldMachineId, Day(tblTempData.fldLogDate);
and have another table to collect all TimeIN1, TimeOUT1, TimeIN2, TimeOUT2.
as much as possible i would like to build all process in sql side.
Lance
Last edited by lance1578; Jan 7th, 2018 at 09:28 AM.
Reason: update the code
then I used a Transform query to get the Data into one Row like this..
Code:
TRANSFORM First(tblLog.fldLogDate) AS [Der Wert]
SELECT Format([fldLogDate],"yyyy") AS mYear, Format([fldLogDate],"mm") AS mMonth, Format([fldLogDate],"dd") AS mDay
FROM tblLog
GROUP BY Format([fldLogDate],"yyyy"), Format([fldLogDate],"mm"), Format([fldLogDate],"dd")
ORDER BY Format([fldLogDate],"hh:nn")
PIVOT Format([fldLogDate],"hh:nn");
here a Image of the results. you could pass the Data to a new Table and perhaps sort it further
regards
Chris
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.
Re: Inserting and Processing Data from one table to another table
With good old VBA and ISAM (access 2003)
Code:
Option Compare Database
Dim db As DAO.Database
Dim rsTempData As DAO.Recordset
Dim rsPData As DAO.Recordset
Sub doit()
Set db = CurrentDb
Set rsPData = db.OpenRecordset("PData", dbOpenTable)
rsPData.Index = "MachLog"
Set rsTempData = db.OpenRecordset("TempData", dbOpenTable)
Do While Not rsTempData.EOF
rsPData.Seek "=", rsTempData.Fields("Mach"), DateValue(rsTempData.Fields("LogDate"))
If rsPData.NoMatch Then
rsPData.AddNew
rsPData.Fields("Mach") = rsTempData.Fields("Mach")
rsPData.Fields("Logdate") = DateValue(rsTempData.Fields("LogDate"))
rsPData.Update
rsPData.Bookmark = rsPData.LastModified
Updateit
Else
Updateit
End If
rsTempData.MoveNext
Loop
rsTempData.Close
Set rsTempData = Nothing
rsPData.Close
Set rsPData = Nothing
db.Close
Set db = Nothing
End Sub
Sub Updateit()
Dim InOut As Byte
Dim TheTime As Date
InOut = rsTempData.Fields("Status")
TheTime = TimeValue(rsTempData.Fields("LogDate"))
Select Case InOut
Case 1 'in
rsPData.Edit
If TheTime < #11:00:00 AM# Then
rsPData.Fields("in1") = TheTime
Else
rsPData.Fields("in2") = TheTime
End If
rsPData.Update
Case 2 'out
rsPData.Edit
If TheTime > #10:00:00 AM# And TheTime < #2:00:00 PM# Then
rsPData.Fields("out1") = TheTime
Else
rsPData.Fields("out2") = TheTime
End If
rsPData.Update
End Select
End Sub
do not put off till tomorrow what you can put off forever