[RESOLVED] Inserting and Processing Data from one table to another table-VBForums
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: 193
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
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,429

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  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
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    849

    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
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    849

    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
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    849

    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: 33
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
    Fanatic Member
    Join Date
    Jun 2014
    Posts
    1,019

    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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.