If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combine update queries into one query
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 |
#2
|
|||
|
|||
Combine update queries into one query
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 |
#3
|
|||
|
|||
Combine update queries into one query
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 |
#4
|
|||
|
|||
Combine update queries into one query
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 |
#5
|
|||
|
|||
Combine update queries into one query
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 . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Update another table with a Max record query | Ngan | Running & Setting Up Queries | 2 | June 22nd, 2004 05:01 PM |
Update Query Problem | Casa | General Discussion | 1 | June 14th, 2004 04:17 PM |
Need help! Parameter update query to form | Le Tran | New Users | 7 | June 8th, 2004 06:26 PM |
Update Queries using queries | Vel | Running & Setting Up Queries | 7 | June 4th, 2004 09:21 PM |
Queries painfully slow, some general pointers please? | Graham R Seach | New Users | 0 | June 1st, 2004 05:19 AM |