I have tried both of the solution posted and they both
work. Thank you!

rp


-----Original Message-----
both better solutions than mine. why didn't i think of
that?


"Steve Schapel" wrote in message
...
RP,

Try this...
UPDATE tblEmployees SET ShiftID = Abs([ShiftID]*
([ShiftID]8))+1

or this...
UPDATE tblEmployees SET ShiftID = IIf([ShiftID]=8,1,
[ShiftID]+1)

--
Steve Schapel, Microsoft Access MVP


wrote:
I have a schedule database that uses 9 update queries
to "rotate" the schedule. The first query changes all
records with a ShiftID of 8 to 9. the next query
changes 7
to 8, next from 6 to 7, etc until the final query
changes
9 to 1. There are 8 shifts. SQL statement from one of
the
queries is UPDATE tblEmployees SET
tblEmployees.ShiftID = 9
WHERE (((tblEmployees.ShiftID)=8));

The queries are run by a macro named mcrRotate. Is
there a
way to make these changes with one query instead of
nine?
Can code be used instead? Thanks in advance.

RP


.