update query

[3.6z]

Hi guys

I would like to create a update query that updates more than one fields at a time. I already created a update query for each feild that i want to update. I have 6 feilds to update which i everyday update them in 6 saparate queries and i would like to make my job easier and update them in one query. here is what i used for my queries

first query

UPDATE TJ_Table1 SET TJ_Table1.Channel = "OTC"
WHERE (((TJ_Table1.BRCH)<100) AND ((TJ_Table1.TELLER)>100));

second query

UPDATE TJ_Table1 SET TJ_Table1.Channel = "IVR"
WHERE (((TJ_Table1.BRCH)=>76));

...etc

any help would be appreciated.

 

[jzwp22]

Since you have different criteria for each query, they cannot be combined into 1.

BTW, your second query will overight the changes made for some of the records updated by the first query. i.e. for those records between 76 but <100. So you might reconsider rewriting the the first to

UPDATE TJ_Table1 SET TJ_Table1.Channel = "OTC"
WHERE (((TJ_Table1.BRCH)<76) AND ((TJ_Table1.TELLER)>100));

If you want help with the code please post back.

 

[3.6z]

my code is working fine and i'm getting the results that i want, but i wanna make it easier since i'm using 6 updated queries to update a table. however, i'm wondering if there is a better way to combine all queries and make a simple query that do the job.

BTW is it possible to use the iif statement in "update to" like iif(TJ_Table.channel="OTC", andiif TJ_Table.channel="IVR" elseiif TJ_Table.channel="TBK" ??

thanks

 

[Brianwarnock]

Yes you can use an IIf in the update to , however your syntax is not correct and given the apparent complexity of your selection it may be batter to write a function and use that. ie =myfunction([myfield])

Brian

 

[jzwp22]

I agree with the Brian in that an update query with an IIF() function might be quite complex. To do it in code, it actually would be fairly simple


Dim myUpdateSQL1 as string
Dim myUpdateSQL2 as string

myUpdateSQL1=”UPDATE TJ_Table1 SET TJ_Table1.Channel = ‘OTC’ WHERE (((TJ_Table1.BRCH)<100) AND ((TJ_Table1.TELLER)>100))”
CurrentDb.Execute myUpdateSQL1, dbFailOnError

myUpdateSQL2=”UPDATE TJ_Table1 SET TJ_Table1.Channel = ‘OTC’ WHERE (((TJ_Table1.BRCH)<100) AND ((TJ_Table1.TELLER)>100))”
CurrentDb.Execute myUpdateSQL2, dbFailOnError

You would continue the same pattern for the remaining 4 queries


There are some nicer ways to do this but this is the basic way & will get the job done.

You would create a new public procedure and type in the code I presented above & you can then call the procedure from anywhere in your application

 

[3.6z]

i have no idea of how to use VB code i tried to compile and says syntax error. could you please explain it in more detail.

thanks

 

[Brianwarnock]

I was not thinking of doing the SQL in the function but using the function to return the cod required. A function would allow you to use If..Then...Elseif.. block or Select Case, I do not know how many fields are involved in the tests, you show atleast 2 brch and teller. I also don't understand how you can have 2 conditions applying ie Brch from 76 to 99 and teller >100 can be OTC or IVR !! you would need to get your tests in the correct sequence.


This function would be coded in Module1 and in the update grid for the field Channel the update to would be =fgetchannel([BRCH],[TELLER])

Example using Select case

Public function fgetchannel(intBrch as integer, intTeller as integer) as string

Select case intBrch

	Case  is => 76
		Fgetchannel =”IVR”

	Case is <100
		Select Case intTeller 
			Case is >100
				Fgetchannel=”OTC”
		End Select

	Case…


	Case Else 
		Fgetchannel=”Unknown”

End function

Brian

 

[jzwp22]

If you can provide a copy of your DB and identify the 6 queries I would be willing to write the code for you.

 

[3.6z]

ok, i have a table called TJ_Table1 and it contains the following fields:

TJ_Table1:
JNL_Date
Amount
BRCH
TELLER
CUSTOMER
CHANNEL
AMTFIX

To update this table, i'm using 6 queries called:
-update channel otc
UPDATE TJ_Table1 SET TJ_Table1.Channel = "OTC"
WHERE (((TJ_Table1.BRCH)<100) AND ((TJ_Table1.TELLER)>100));

-update channel atm
UPDATE TJ_Table1 SET TJ_Table1.Channel = "ATM"
WHERE (((TJ_Table1.BRCH)<100) AND ((TJ_Table1.TELLER)<100));

-update channel oth
UPDATE TJ_Table1 SET TJ_Table1.Channel = "OTHER"
WHERE (((TJ_Table1.Channel) Is Null));

-update channel ivr
UPDATE TJ_Table1 SET TJ_Table1.Channel = "IVR"
WHERE (((TJ_Table1.BRCH)=76));

-update channel onl
UPDATE TJ_Table1 SET TJ_Table1.Channel = "ONL"
WHERE (((TJ_Table1.BRCH)=95));

-update channel tbk
UPDATE TJ_Table1 SET TJ_Table1.Channel = "TBK"
WHERE (((TJ_Table1.BRCH)=77));

To show the results of all these queries i made a table called TJ_AMOUNT BY CHANNEL. This table shows the update on otc, atm, oth, tbk, ivr, and onl. here is the sql for the above table:

TRANSFORM Sum(TJ_Table1.AMOUNT) AS SumOfAMOUNT
SELECT TJ_Table1.JNL_DATE, Sum(TJ_Table1.AMOUNT) AS [Total Of AMOUNT]
FROM TJ_Table1
GROUP BY TJ_Table1.JNL_DATE
PIVOT TJ_Table1.Channel;

My question is there any way to make one updated query instead of 6 queries?

Thank you,

 

[jzwp22]

Attached is an example database with the code to conduct the updates.