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

[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

[ChipR]

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.

[ADezii]

@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.