Compare data between row, move to new table and lastly delete it from old table

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • beemomo
    New Member
    • Jan 2008
    • 50
    #1

    Compare data between row, move to new table and lastly delete it from old table

    Hi everyone here,

    Wondering if this can be perform in access using SQL or vba.

    VesselMovement table:
    RNO Date_Fix TIME VID X Y HEADING SPEED
    1 07/05/2009 00:00 605 5.1896 6.7198 81 0.3
    2 07/05/2009 00:30 605 5.1896 6.7198 90 0.4
    3 07/05/2009 01:00 605 5.1847 6.7193 0 4.0
    ...
    10 07/05/2009 18.00 605 5.1956 6.7153 10 9.0
    11 08/05/2009 08:00 605 5.2145 6.4533 78 4.0
    12 08/05/2009 08:30 605 5.2145 6.4533 56 0.3
    13 08/05/2009 09:00 605 5.7892 6.6723 17 0.5
    14 03/01/2009 04:00 611 5.7891 6.7893 19 0.4
    15 03/01/2009 04:30 611 5.7891 6.7893 21 0.0
    16 03/01/2009 05:00 611 5.2345 6.9856 0 0.9

    For specific VID on particular day, I need to select:

    ****check until the last record found for the particular VID on specific day.
    **** eg. for 605 on 08/05/2009, if will check until

    if SPEED(on row1) <= 0.5 AND SPEED(on row2) <= 0.5 then
    if X(row1) = X(row2) and Y(row1) = Y(row2) then
    move RNO11 to a new table
    delete RNO11 from table VesselMovement

    elseif SPEED(on row2) <= 0.5 AND SPEED(on row3) <= 0.5 then
    if X(row2) = X(row3) and Y(row2) = Y(row3) then
    move RNO12 to a new table.
    delete RNO12 from table VesselMovement

    Hope to hear from you guys as soon as possible.
    Thank you very much.

    Regards,
    Beemomo
    Tags: None
    • ChipR
      Recognized Expert Top Contributor
      • Jul 2008
      • 1289
      #2
      You should create a recordset and step through it in vba that follows this pseudocode. Let us know if you have trouble with a specific part of this.

        Comment

        • ADezii
          Recognized Expert Expert
          • Apr 2006
          • 8834
          #3
          Originally posted by beemomo
          Hi everyone here,

          Wondering if this can be perform in access using SQL or vba.

          VesselMovement table:
          RNO Date_Fix TIME VID X Y HEADING SPEED
          1 07/05/2009 00:00 605 5.1896 6.7198 81 0.3
          2 07/05/2009 00:30 605 5.1896 6.7198 90 0.4
          3 07/05/2009 01:00 605 5.1847 6.7193 0 4.0
          ...
          10 07/05/2009 18.00 605 5.1956 6.7153 10 9.0
          11 08/05/2009 08:00 605 5.2145 6.4533 78 4.0
          12 08/05/2009 08:30 605 5.2145 6.4533 56 0.3
          13 08/05/2009 09:00 605 5.7892 6.6723 17 0.5
          14 03/01/2009 04:00 611 5.7891 6.7893 19 0.4
          15 03/01/2009 04:30 611 5.7891 6.7893 21 0.0
          16 03/01/2009 05:00 611 5.2345 6.9856 0 0.9

          For specific VID on particular day, I need to select:

          ****check until the last record found for the particular VID on specific day.
          **** eg. for 605 on 08/05/2009, if will check until

          if SPEED(on row1) <= 0.5 AND SPEED(on row2) <= 0.5 then
          if X(row1) = X(row2) and Y(row1) = Y(row2) then
          move RNO11 to a new table
          delete RNO11 from table VesselMovement

          elseif SPEED(on row2) <= 0.5 AND SPEED(on row3) <= 0.5 then
          if X(row2) = X(row3) and Y(row2) = Y(row3) then
          move RNO12 to a new table.
          delete RNO12 from table VesselMovement

          Hope to hear from you guys as soon as possible.
          Thank you very much.

          Regards,
          Beemomo
          This is an interesting problem which may require a slightly complex solution. If you are patient (not rushed for a solution), I'll be more than happy to walk you through each step of what hopefully will be a viable solution. On the other hand, if you are pressed for time, I can be of no further assistance to you. Let me know either way.

            Comment

            Previous template Next
            Working...