Run 3 update queries at once

[ironwood9 via AccessMonster.com]

I have a table with three Yes/No fields used as “flags†…. I run a update
queries to set the value of these fields to True if certain criteria is met.
There are three update queries with slightly different criteria, and each one
updates a different one of the 3 yes/no fields – is there a way to combine
the 3 queries ?
The function named fNetWorkDays calculates the number of working days
– if the number equals 3, then I want to check the “Call1†field in tblMain.
If the number equals 6, then I want to check the “Call2†field, and so on –
here’s the code for the 3 queries:

Query 1:
UPDATE tblMain SET tblMain.Call1 = -1, tblMain.DateUpdated = Now()
WHERE (((tblMain.Call1)=0) AND ((fNetWorkdays([DtStartDate],Date(),True))=
3))

Query 2:
UPDATE tblMain SET tblMain.Call2 = -1, tblMain.DateUpdated = Now()
WHERE (((tblMain.Call2)=0) AND ((fNetWorkdays([DtStartDate],Date(),True))=
6));

Query 3:
UPDATE tblMain SET tblMain.Call3 = -1, tblMain.DateUpdated = Now(),
WHERE (((tblMain.Call3)=0) AND ((fNetWorkdays([DtStartDate],Date(),True))=
9));

 

[ironwood9 via AccessMonster.com]

I have a table with three Yes/No fields used as “flags†…. I run a update
queries to set the value of these fields to True if certain criteria is met.
There are three update queries with slightly different criteria, and each one
updates a different one of the 3 yes/no fields – is there a way to combine
the 3 queries ?
The function named fNetWorkDays calculates the number of working days
– if the number equals 3, then I want to check the “Call1†field in tblMain.
If the number equals 6, then I want to check the “Call2†field, and so on –
here’s the code for the 3 queries:

Query 1:
UPDATE tblMain SET tblMain.Call1 = -1, tblMain.DateUpdated = Now()
WHERE (((tblMain.Call1)=0) AND ((fNetWorkdays([DtStartDate],Date(),True))=
3))

Query 2:
UPDATE tblMain SET tblMain.Call2 = -1, tblMain.DateUpdated = Now()
WHERE (((tblMain.Call2)=0) AND ((fNetWorkdays([DtStartDate],Date(),True))=
6));

Query 3:
UPDATE tblMain SET tblMain.Call3 = -1, tblMain.DateUpdated = Now(),
WHERE (((tblMain.Call3)=0) AND ((fNetWorkdays([DtStartDate],Date(),True))=
9));

 

[Bob Barrows]

I have a table with three Yes/No fields used as "flags" .. I run a
update queries to set the value of these fields to True if certain
criteria is met. There are three update queries with slightly
different criteria, and each one updates a different one of the 3
yes/no fields - is there a way to combine the 3 queries ?
The function named fNetWorkDays calculates the number of
working days - if the number equals 3, then I want to check the
"Call1" field in tblMain. If the number equals 6, then I want to
check the "Call2" field, and so on - here's the code for the 3
queries:

Query 1:
UPDATE tblMain SET tblMain.Call1 = -1, tblMain.DateUpdated =
Now() WHERE (((tblMain.Call1)=0) AND
((fNetWorkdays([DtStartDate],Date(),True))= 3))

Query 2:
UPDATE tblMain SET tblMain.Call2 = -1, tblMain.DateUpdated =
Now() WHERE (((tblMain.Call2)=0) AND
((fNetWorkdays([DtStartDate],Date(),True))= 6));

Query 3:
UPDATE tblMain SET tblMain.Call3 = -1, tblMain.DateUpdated =
Now(), WHERE (((tblMain.Call3)=0) AND
((fNetWorkdays([DtStartDate],Date(),True))= 9));

Well, it might be possible, but it might not perform as well as running
the 3 separate queries would. You see, each of those 3 queries is
limiting the rows affected by the value of the CallN field, thus saving
you from scanning the entire table. In order to combine these updates
into one, we are now going to be forced to scan the entire table. Here
is one way to do it:

UPDATE tblMain
SET
[Call1] = Iif(fNetWorkdays([DtStartDate],Date(),True)= 3,-1,[Call1] ),
[Call2] = Iif(fNetWorkdays([DtStartDate],Date(),True)= 6,-1,[Call2] ),
[Call3] = Iif(fNetWorkdays([DtStartDate],Date(),True)= 9,-1,[Call3] ),
DateUpdated =Now()
WHERE fNetWorkdays([DtStartDate],Date(),True) IN (3,6,9)

Give it a try and, if it takes forever to run, don't say I didn't warn
you

 

[ironwood9 via AccessMonster.com]

Bob,
Thanks ! Actually, the version I'm using right now is a scaled down version,
so as for performance, I don't think it's going to be an issue anyway - but
I'll have to cross that bridge.

One thing I left out is the fact that IF the Call3 field is being set to -1,
THEN I want another field (LastCallDt) in tbl Main to be populated with the
current date. Is that possible ?

Thanks for your help !

I have a table with three Yes/No fields used as "flags" .. I run a
update queries to set the value of these fields to True if certain

[quoted text clipped - 21 lines]

Now(), WHERE (((tblMain.Call3)=0) AND
((fNetWorkdays([DtStartDate],Date(),True))= 9));

Well, it might be possible, but it might not perform as well as running
the 3 separate queries would. You see, each of those 3 queries is
limiting the rows affected by the value of the CallN field, thus saving
you from scanning the entire table. In order to combine these updates
into one, we are now going to be forced to scan the entire table. Here
is one way to do it:

UPDATE tblMain
SET
[Call1] = Iif(fNetWorkdays([DtStartDate],Date(),True)= 3,-1,[Call1] ),
[Call2] = Iif(fNetWorkdays([DtStartDate],Date(),True)= 6,-1,[Call2] ),
[Call3] = Iif(fNetWorkdays([DtStartDate],Date(),True)= 9,-1,[Call3] ),
DateUpdated =Now()
WHERE fNetWorkdays([DtStartDate],Date(),True) IN (3,6,9)

Give it a try and, if it takes forever to run, don't say I didn't warn
you

 

[Bob Barrows]

It's pretty much the same formula as that used to update Call3 (with two
changes). See if you can figure it out.

Bob,
Thanks ! Actually, the version I'm using right now is a scaled down
version, so as for performance, I don't think it's going to be an
issue anyway - but I'll have to cross that bridge.

One thing I left out is the fact that IF the Call3 field is being set
to -1, THEN I want another field (LastCallDt) in tbl Main to be
populated with the current date. Is that possible ?

Thanks for your help !

I have a table with three Yes/No fields used as "flags" .. I run a
update queries to set the value of these fields to True if certain

[quoted text clipped - 21 lines]

Now(), WHERE (((tblMain.Call3)=0) AND
((fNetWorkdays([DtStartDate],Date(),True))= 9));

Well, it might be possible, but it might not perform as well as
running
the 3 separate queries would. You see, each of those 3 queries is
limiting the rows affected by the value of the CallN field, thus
saving
you from scanning the entire table. In order to combine these updates
into one, we are now going to be forced to scan the entire table.
Here
is one way to do it:

UPDATE tblMain
SET
[Call1] = Iif(fNetWorkdays([DtStartDate],Date(),True)= 3,-1,[Call1]
), [Call2] = Iif(fNetWorkdays([DtStartDate],Date(),True)=
6,-1,[Call2] ), [Call3] =
Iif(fNetWorkdays([DtStartDate],Date(),True)= 9,-1,[Call3] ),
DateUpdated =Now()
WHERE fNetWorkdays([DtStartDate],Date(),True) IN (3,6,9)

Give it a try and, if it takes forever to run, don't say I didn't
warn
you

 

[ironwood9 via AccessMonster.com]

Bob,
Would it be:
[Call3] = Iif(fNetWorkdays([LastCallDt],Date(),True)= 9,Date(),[Call3] ),

???

It's pretty much the same formula as that used to update Call3 (with two
changes). See if you can figure it out.

Bob,
Thanks ! Actually, the version I'm using right now is a scaled down

[quoted text clipped - 35 lines]

 

[Bob Barrows]

Well ... no. The field you want to update is not [Call3], is it. And you
don't want to set that field's value to [Call3] 's value, do you?

OK, you've given it a try, so here's the answer:

[LastCallDt] = Iif(fNetWorkdays([DtStartDate],Date(),True)=
9,Date(),[LastCallDt] )

In English, this says:
set the value of LastCallDt to today's date if
fNetWorkdays([DtStartDate],Date(),True)= 9, otherwise leave it alone (set
its value to itself)

Bob,
Would it be:
[Call3] = Iif(fNetWorkdays([LastCallDt],Date(),True)=
9,Date(),[Call3] ),

???

It's pretty much the same formula as that used to update Call3 (with
two changes). See if you can figure it out.

Bob,
Thanks ! Actually, the version I'm using right now is a scaled down

[quoted text clipped - 35 lines]

warn
you

 

[ironwood9 via AccessMonster.com]

Bob,
I tried, sorry - I know the syntax for one immediate if, where you have the
arg,the condition, and the result if true, if false, but in this case, I
don't get it - I don't understand why you put the field name twice -
obviously you do...

In English, the one part says:
Set the field named Call3 equal to TRUE IF the result of the function is
equal to 9, and leave it set to FALSE if the result of the function's
calculation is NOT equal to 9.

I get the first part - the what - like SET fieldname = to whatever, but
what's the significance of stating the fieldname again ?

???

It's pretty much the same formula as that used to update Call3 (with two
changes). See if you can figure it out.

[quoted text clipped - 3 lines]

 

[Bob Barrows]

Did my previous reply help?

Bob,
I tried, sorry - I know the syntax for one immediate if, where you
have the arg,the condition, and the result if true, if false, but in
this case, I don't get it - I don't understand why you put the field
name twice - obviously you do...

In English, the one part says:
Set the field named Call3 equal to TRUE IF the result of the function
is equal to 9, and leave it set to FALSE if the result of the
function's calculation is NOT equal to 9.

I get the first part - the what - like SET fieldname = to whatever,
but what's the significance of stating the fieldname again ?

???

It's pretty much the same formula as that used to update Call3
(with two changes). See if you can figure it out.

[quoted text clipped - 3 lines]

warn
you

 

[ironwood9 via AccessMonster.com]

Bob,
Yes, it did ! and now that I look at it again, I see what you mean about the
syntax.

BTW, side note - I know that it's considered 'sleazy' to post on more than
one board right from the get-go, using cut-n-paste, and I did not do that
here. I did post on another board where you can see how many views your
question got, and mine was on there for hours, got several views and not one
reply, so I figured it would be ok to post here - so kudos for knowing how to
do this, because I didn't have a clue. Actually, I did get one reply, which
in a nutshell was that they thought it couldn't be done.

Thanks so much !!!!!!

------------------------------------------------------------------------------
--------------------------------------

Did my previous reply help?

Bob,
I tried, sorry - I know the syntax for one immediate if, where you

[quoted text clipped - 17 lines]