I have a table "estimate". It contains the fields:
I need to update the "priorpeer" column. The query needs to use the "prefix" column to determine the update. The rules are:
if prefix = 0 then "priorpeer" = -1
if itemno ends in .0 then "priorpeer" = -1
if the first instance of the prefix (when the query is sorted by itemno) then "priorpeer" = -1
otherwise, while still sorted by itemno, the "priorpeer" = assemblyseq of the prior record with the matching prefix.
See the attached excel spreadsheet for the desired update to "priorpeer" field. column "H" shows what I want the end update result to be.
Thanks for your help.