Results 1 to 9 of 9
  1. #1
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151

    Question Append Query to copy 3rd record from table

    Hello all.



    I have an XML file that I am importing in to my DB. During import, due to the structure of the XML file a number of tables are created in my DB.

    I am using an append query to pick the data I need from some of the XML created tables (I don't need all the data), to fill data in another master table, named MachineSettings.

    Most of the XML created tables only contain a single record, which is fine. However one of the tables (called Origins) contains 6 records, and I only need the 3rd record from this table to be appended to my MachineSettings table.

    Is there a way I can modify the SQL to achieve this?

    Code:
    INSERT INTO _MachineSettings ( MachineName, A2MCNo, FeedRateMAX, PlungeRateMAX, TravelRateMAX, TravelRate, PlungeRate, FeedRate, JogSpeedMode, SeekXYSpeed, SeekZSpeed, JerkGrate, AccelerationG, AccelMAX, JerkMAX, CentripetalG, BrakeG, ArcError, MinLength, x, y, z )
    SELECT MachineInformation.MachineName, Model.Code, MotionParameters.FeedRateMAX, MotionParameters.PlungeRateMAX, MotionParameters.TravelRateMAX, MotionParameters.TravelRate, MotionParameters.PlungeRate, MotionParameters.FeedRate, MotionParameters.JogSpeedMode, MotionParameters.SeekXYSpeed, MotionParameters.SeekZSpeed, MotionParameters.JerkGrate, MotionParameters.AccelerationG, MotionParameters.AccelMAX, MotionParameters.JerkMAX, MotionParameters.CentripetalG, MotionParameters.BrakeG, MotionParameters.ArcError, MotionParameters.MinLength, Origin.x, Origin.y, Origin.z
    FROM MachineInformation, MotionParameters, Model, Origin;
    Any suggestions greatly appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    use the sql to make a recordset, then get the 3rd record:

    Code:
    dim rst
    dim I as long
    
    set rst = currentdb.openrecordset("select * from table")
    I = 1
    with rst
      while not .eof and I<3
         I = I + 1
         .movenext
      wend
    
        vID = .fields("ID").value
        msgbox vID
     
       'now you have the rec ID, use it however you wish (include the ID so you can go straight to that record.
    end with

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need to be careful about order. The third record based on what order? Ideally you need a unique field so the order is unambiguous

    in sql you can do it using the TOP predicate

    Code:
    SELECT TOP 1 *
    FROM myTable WHERE uniquefield not in (SELECT TOP 2 uniquefield from myTable AS T ORDER BY uniquefield)
    ORDER BY uniquefield
    or perhaps your origins table has some sort of identifier field in which case you can then use a simple WHERE clause

  4. #4
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi Ranman

    Thanks for the reply. I'll need to digest this because I've never coded like this before, though I do understand what it is doing.

    I've managed to get the result I want by using your suggestion.

    Code:
    Private Sub Get4thRecFromOrigin_Click()
    Dim rst
    Dim I As Long
    
    Set rst = CurrentDb.OpenRecordset("select * from XMLMachineSettingsImportTemp")
    I = 1
    With rst
      While Not .EOF And I < 4
         I = I + 1
         .MoveNext
      Wend
    
        ' vID = .Fields("x").Value & " " & .Fields("y").Value & " " & .Fields("z").Value
        vID = .Fields("x").Value
        MsgBox vID
     
        'now you have the rec ID, use it however you wish (include the ID so you can go straight to that record.
    
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO XMLMachineSettingsImport SELECT * FROM XMLMachineSettingsImportTemp WHERE x = '" & vID & "'"
        DoCmd.RunSQL "Delete * From [XMLMachineSettingsImportTemp]"
        DoCmd.SetWarnings True
    
    End With
    
    End Sub
    Interestingly I found that if I ran the code multiple times it would not update the XMLMachineSettingsImportTemp table, which I expected it would do, and is what I want it to do. I solved this by adding this code after the insert command and then repopulating the XMLMachineSettingsImportTemp table, which is what would really be happening when I finish the project.

    I'd like to know why this happens though, just for my own edification.

    Code:
        DoCmd.RunSQL "Delete * From [XMLMachineSettingsImportTemp]"
    Now this leads to another potential issue.

    We have thousands of machines whose data we are wanting to record. They all generate the same XML file. So I plan on deleting all the temporarily created tables after a customers' settings are imported. This will keep a lot of unwanted data out of the DB. However I am aware that

    1. This approach can cause the DB size to blow out, so compact/repair needs to be performed occasionally. What is the effect of doing this if multiple users are logged in to the DB? Is there a way around this?
    2. Is there a potential problem with the creating/deleting of tables in a multi-user environment, which this is. The DB can be opened by multiple people at once.


    Thanks for the help and sorry for the questions. It seems the more I learn the more I need to learn.
    Last edited by sheusz; 02-13-2020 at 09:11 PM.

  5. #5
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi Ajax
    Thanks for the reply.

    The XML file looks like this.

    <Origin>
    <x>-3.5486344034499289</x>
    <y>0.0019166267369429802</y>
    <z>0</z>
    </Origin>
    <OriginList>
    <Origin>
    <x>0</x>
    <y>0</y>
    <z>0</z>
    </Origin>
    <Origin>
    <x>17.65</x>
    <y>8.283</y>
    <z>0</z>
    </Origin>
    <Origin>
    <x>6.146</x>
    <y>7.598</y>
    <z>0</z>
    </Origin>
    <Origin>
    <x>0</x>
    <y>0</y>
    <z>0</z>
    </Origin>
    <Origin>
    <x>0</x>
    <y>0</y>
    <z>0</z>
    </Origin>
    <Origin>
    <x>0</x>
    <y>0</y>
    <z>0</z>
    </Origin>
    </OriginList>

    When I import the data in to the DB, this section of the XML file creates a table called Origin, with 3 fields (x, y and z) then fills the table with the x, y and z data for each origin value.

    It is purely linear and for each machine the values will vary wildly, so sorting is not an option. I just need to count to the 3rd record in the origins table and get that value. In the future I may need to extract the other values.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I just have a concern that records are stored randomly, but if you are happy that the order in the table reflects the order in your XML file then you could try this SQL

    Code:
    SELECT Last(x) as xVal, Last(y) as yVal, Last(z) as zVal
    FROM (SELECT TOP 3 * from tblOrigin) AS T

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    another option perhaps is to not import as a table but import as a string then use vba code in a function to split the string on '<x' and find the 3rd? element then use string manipulation to extract the 3 values

  8. #8
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    151
    Hi Ajax

    I'm confident that the order of data and the XML is correct. But will be closely monitoring during testing. I have a solution that is working. Please see my edited reply to Ranmam.

    It's raised some functional questions on which I would appreciate some input.

    I've built a quick form that shows the process. In the end it will all be automatic. I just find that this approach helps me in stepping through the logic.

    Code:
    Private Sub ImportFormatData_Click()
    Application.ImportXML _
     DataSource:="c:\XMLMachineAdjustableParams.xml", _
     ImportOptions:=acStructureAndData
    End Sub
    
    Private Sub AppendData_Click()
    
    Application.ImportXML _
     DataSource:="c:\XMLMachineAdjustableParams.xml", _
     ImportOptions:=acAppendData
     
    End Sub
    
    Private Sub UpdateMachineSettingsTable_Click()
    ' copy records from XML tables to _MachineSettings table
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryPopulateTempTable", , acReadOnly
    DoCmd.SetWarnings True
    ' delete records from Origin table
    'DoCmd.RunSQL "Delete * From [Origin]"
    
    End Sub
    
    Private Sub Get4thRecFromOrigin_Click()
    Dim rst
    Dim I As Long
    
    Set rst = CurrentDb.OpenRecordset("select * from XMLMachineSettingsImportTemp")
    I = 1
    With rst
      While Not .EOF And I < 4
         I = I + 1
         .MoveNext
      Wend
    
        ' vID = .Fields("x").Value & " " & .Fields("y").Value & " " & .Fields("z").Value
        vID = .Fields("x").Value
        MsgBox vID
     
        'now you have the rec ID, use it however you wish (include the ID so you can go straight to that record.
    
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO XMLMachineSettingsImport SELECT * FROM XMLMachineSettingsImportTemp WHERE x = '" & vID & "'"
        DoCmd.RunSQL "Delete * From [XMLMachineSettingsImportTemp]"
        DoCmd.SetWarnings True
    
    End With
    
    End Sub

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    no problem - whatever works. Your original requirement was for a sql solution which is what I was providing

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 06-13-2019, 08:01 PM
  2. Replies: 11
    Last Post: 10-12-2017, 07:14 AM
  3. Replies: 2
    Last Post: 09-14-2017, 05:09 PM
  4. Replies: 4
    Last Post: 01-20-2015, 12:55 PM
  5. copy and append a record
    By erikl1 in forum Access
    Replies: 4
    Last Post: 03-02-2012, 03:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums