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