Run 3 update queries at once

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));

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

ironwood9 wrote:
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));

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

ironwood9 via AccessMonster.com wrote:
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 :-)


--
HTH,
Bob Barrows

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 !


Bob Barrows wrote:
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 :-)


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

It's pretty much the same formula as that used to update Call3 (with two
changes). See if you can figure it out.
ironwood9 via AccessMonster.com wrote:
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 !


Bob Barrows wrote:
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 :-)

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

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

???


Bob Barrows wrote:
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 :-)


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

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)



ironwood9 via AccessMonster.com wrote:
Bob,
Would it be:
[Call3] = Iif(fNetWorkdays([LastCallDt],Date(),True)=
9,Date(),[Call3] ),

???


Bob Barrows wrote:
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 :-)

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

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 :-)

--
Message posted via http://www.accessmonster.com

Did my previous reply help?


ironwood9 via AccessMonster.com wrote:
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 :-)

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"