In general, this NEVER works, though you can make workarounds that muddle through. But the question betrays unawareness of some factors - some theoretical, some practical.
First, tables are not ordered. Their keys are ordered, and if you specify a sort order on a field, the query that returns the table's records will present them in some predictable order. But if you don't sort the query, it is impossible to meaningfully define "previous."
Second, for the first record there is NO previous query no matter how you sort the query. So what do you do to that record? Where does it get its value? Thin air? If so, you will propagate thin air if you run this operation more than once.
Third, SQL is the basis for queries. The strict definition of SQL is such that EACH RECORD EXISTS ON ITS OWN. (If it exists at all.) SQL will return a SET (agglomerated) of records based on criteria and will present them in some order defined by a SORT selection or the phase of the moon if you didn't define a SORT order. So "previous" again has no real meaning using the strict SQL definitions for recordsets.
Fourth, this betrays a non-normalized table setup. When you say that a value in record X should be written to record X+1, what you are REALLY saying is that the value you are distributing DOES NOT DEPEND ON THE TABLE'S KEY. So why is it in that table in the first place, if you were shooting for normalized records? (If you WEREN'T going for proper normalization, that is your call and go for it... but it ain't a good idea.)
Having now admonished you for what is probably going to be a design that bites you on the butt sometime in the future, I can tell you that the problem has been addressed before. You aren't the first one to run afoul of this particular need.
Search this forum for "previous record" or "prior record" or "prior field value" as search topics. Last I recall seeing this was months ago, so don't make your search a short-ranged one.
