Results 1 to 12 of 12

Thread: [RESOLVED] Inserting and Processing Data from one table to another table

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Aug 2017
    Posts
    22

    Resolved [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
    Name:  Capture_020.jpg
Views: 496
Size:  49.0 KB
    Thank you very much.
    Last edited by lance1578; Jan 6th, 2018 at 10:13 AM. Reason: correction on code

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Inserting and Processing Data from one table to another table

    This question has nothing to do with VB.NET. I've asked the mods to move it to Database Development forum.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Aug 2017
    Posts
    22

    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.

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

    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.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Aug 2017
    Posts
    22

    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.

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Aug 2017
    Posts
    22

    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. :-)

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

    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.

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Aug 2017
    Posts
    22

    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

    ex1:
    MacID | Year | Month | Day | TimeIn1 | TimeOUT1 | TimeIN2 | TimeOUT2
    2 2017 7 20 7:05 11:25 12:30 17:45
    2 2017 7 21 6:59 13:03 17:20
    2 2017 7 22 11:45 17:56
    2 2017 7 23 12:01

    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

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

    Re: Inserting and Processing Data from one table to another table

    Hi Lance,

    don't know if this will help.
    I just took a Table with the Dates Data like this..

    ID fldLogDate
    1 12.12.2018 06:06:13
    2 12.12.2018 09:16:00
    3 12.12.2018 11:30:00
    4 12.12.2018 13:10:00
    5 13.12.2018 09:12:00
    6 15.12.2018 06:30:00
    7 15.12.2018 11:30:00

    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

    Name:  SortDates.jpg
Views: 167
Size:  16.3 KB

    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.

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Aug 2017
    Posts
    22

    Re: Inserting and Processing Data from one table to another table

    @ChrisE

    Thank you very much I give you an update later..

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

    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
    Attached Files Attached Files
    do not put off till tomorrow what you can put off forever

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Aug 2017
    Posts
    22

    Re: Inserting and Processing Data from one table to another table

    @IkkeEnGij

    Thank you very much..I keep you code for future reference..

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