i have a ss_table which consist of sID, qnsID, ansID.

the values of sID are s0001, s0002, s0003 and so on.

If i were to remove s0002, i would like s0003 to shift up and set as s0002.

can someone show me how i can do this in sQL

my approach is rst.Open "SQL", cn, adOpenDynamic, adLockOptimistic


note: sID is set as 'Text' in Access table but autonumber-ed by SQL
rst!sID = "ss" & Right("0000" & CStr(CInt(rst1!ansID) + 1), 4)

Thank You

Astro