Running Multiple Update queries in one

[accessturtle]

Hello

i have 11 diffrent queries for the same table that i run on a daily basis to update, i think there has to be a way of combining them all together. below are an examples of 2 queries that i want to combine.

Query1
UPDATE test SET test.Agent = "Balloon"
WHERE (((test.Agent) Is Null) AND ((test.PaymentType) Not In ("Blue","Red")) AND ((test.Last) Like "c*") AND ((test.State)<>"OH")) OR (((test.Agent) Is Null) AND ((test.PaymentType) Not In ("Blue","Red")) AND ((test.Last) Between "da?*" And "dq?*") AND ((test.State)<>"OH"));

Query2
UPDATE test SET test.Agent = "Balloon"
WHERE (((test.Agent) Is Null) AND ((test.PaymentType) Not In ("Blue","Red")) AND ((test.Last) Like "z*") AND ((test.State)<>"OH")) OR (((test.Agent) Is Null) AND ((test.PaymentType) Not In ("Blue","Red")) AND ((test.Last) Between "aa?*" And "aq?*") AND ((test.State)<>"OH"));

Thank you in advance for help
Access Turtle

 

[pbaldy]

From the look of it, the only differences are in the criteria. If appropriate to the situation, can't you do this type of thing?

...AND (test.Last Like "c*" OR test.Last Like "z*") AND

 

[accessturtle]

first of all thank you very much for the reply, I Am sorry I made a mistake in query2 instead of "balloon" its "king"

 

[pbaldy]

Then to the best of my knowledge, you're stuck with running individual queries. You can certainly run them all from code or a macro to simplify the process. I might also point out that since you are running this every day, and the value of the field is dependent on the value of other fields, it is essentially a calculated field and probably shouldn't exist. Just calculate the appropriate value of it on the fly, either in queries or with a function. More info about what it represents would help clarify that.

 

[accessturtle]

Do you know where Can I find more info on how to combin or execute queries through a macro or a code? Please and thank you

 

[pbaldy]

Personally I'd use VBA code. Presuming there are no form references in the queries, it could be as simple as:

CurrentDb.Execute "Query1"
CurrentDb.Execute "Query2"
CurrentDb.Execute "Query3"

 

[gemma-the-husky]

so what you are trying to do is set the agent, where the agent is null - depending on various other factors

so i would write a function then its something like

update test set agent = functionvalue where agent is null

pass all the test objects into the function, and let the function do it all.

====================
out of interest, i would also look to replace the text values with look up tables, and store numeric values.