none
Inserting and Processing Data from one table to another table RRS feed

  • Question

  • <g class="gr_ gr_4 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="4" id="4">Im</g> trying to process the data from tblTempData to tblPData using the ff <g class="gr_ gr_5 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling ins-del multiReplace" data-gr-id="5" id="5">sql</g>.

    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 <g class="gr_ gr_25 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" data-gr-id="25" id="25">i</g> run that code in MS Access Querry Tools it <g class="gr_ gr_21 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="21" id="21">didnt</g> work as expected. And I <g class="gr_ gr_22 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" data-gr-id="22" id="22">cant</g> really get it right.
    I really appreciate any help on this. 


    tblTempData
    fldMachineId fldLogDate LogStatus
    2 9/7/2017 18:13 2
    2 9/11/2017 7:01 1
    2 9/11/2017 11:33 2
    2 9/11/2017 11:34 1
    2 9/11/2017 17:02 2
    2 9/12/2017 6:32 1
    2 9/14/2017 6:38 1
    2 9/14/2017 11:39 2
    2 9/14/2017 11:40 1
    2 9/14/2017 17:52 2
    2 9/15/2017 7:09 1
    2 9/15/2017 11:40 2
    2 9/15/2017 11:41 1
    2 9/15/2017 17:04 2
    2 9/18/2017 6:29 1
    2 9/19/2017 11:35 2
    2 9/19/2017 11:36 1
    2 9/19/2017 17:06 2
    2 9/20/2017 6:48 1
    2 9/20/2017 17:04 2


    tblPData  
    fldMachineId Year Month Day TimeIn1 TimeOut1 TimeIn2 TimeOut2


    Saturday, January 6, 2018 3:07 PM

All replies

  • Can you please remove the HTML from your text? It is currently unreadable.

    -Tom. Microsoft Access MVP

    Saturday, January 6, 2018 7:01 PM
  • In another thread the member that created the thread said they don't know how to fix it. If you know how to fix it then it will really help if you tell us how.

    Instead of complaining in forums such as this someone should post something in the Suggestions and Feedback for the Forums forum. I have done that now; see Garbage HTML in posts.



    Sam Hobbs
    SimpleSamples.Info

    Saturday, January 6, 2018 8:48 PM
  • Hi Lance5665,

    from the description of the thread, I can see that you have 2 Tables and you want to insert data from one table to another table.

    I check your both tables and your query.

    I find that there are so mistakes in query and Table.

    (1) in Table "tblPData" you had used reserved keywords 'Year' and 'Month' as field name.

    (2) if we see the query then you will find that you had used the fields names which are not available in Tables.

    if you try to run each query separately then you will find that your query contains the syntax error.

    so you can try to review your query and try to correct it to get desired result.

    Reference:

    INSERT INTO Statement (Microsoft Access SQL)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 8, 2018 6:50 AM
    Moderator
  • Hi Lance5665,

    Is your issue resolved?

    I find that, After creating this thread, You did not done any follow up on this thread.

    If your issue is solved then I suggest you to share your solution and mark it as an answer.

    If your issue is still persist then I suggest you to refer the suggestions given by the community members may help you to solve your issue.

    If then after you have any further questions then you can let us know about it.

    We will try to provide further suggestions to solve it.

    I suggest you to update the status of this thread. This thread is still open and will remain open until you mark the answer.

    So take appropriate steps to close this thread , If your issue is resolved.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 18, 2018 9:17 AM
    Moderator