A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Combine update queries into one query



 
 
Thread Tools Display Modes
  #1  
Old June 27th, 2004, 04:58 AM
external usenet poster
 
Posts: n/a
Default 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  
Old June 27th, 2004, 08:41 AM
tina
external usenet poster
 
Posts: n/a
Default 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



  #4  
Old June 27th, 2004, 07:58 PM
tina
external usenet poster
 
Posts: n/a
Default 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  
Old June 30th, 2004, 11:09 AM
rp
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 09:55 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.