you can do it in one update query. you can either save the SQL statement as
a query object, or execute the SQL statement from VBA. i don't know if one
is faster programmatically; if not, the difference is really more "which one
is easier for *you* to modify if you need to".
try using the following expression in the Update To: line of the ShiftID
field in the query, as

Nz(Choose([TableName].[ShiftID], 2, 3, 4, 5, 6, 7, 8, 1), 1)

if the result of the Choose function is Null, the record will be assigned a
ShiftID of 1 as a default. since you're only making one pass thru the
records, you don't need to utilize the temporary 9 value. suggest you read
up on both the Nz() function and the Choose() function in Access Help, so
you'll understand how they work.
*try it out on a COPY of the database first - NOT on live data!*

btw, the following may run slowly if your table is large, because the Choose
function evaluates every choice in the list - it doesn't stop when it finds
a match. instead of the above solution, you could call a user-defined VBA
function that uses the Select Case statement. if you'd like to see that
code, i'll post it too.

hth


wrote in message
...
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