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

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