Combine update queries into one query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
RP,

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

or this...
UPDATE tblEmployees SET ShiftID = IIf([ShiftID]=8,1,[ShiftID]+1)
 
both better solutions than mine. why didn't i think of that? :)


Steve Schapel said:
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


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
Click to expand...
Click to expand...
 
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 said:
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


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
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top