Updating Multiple Rows in one Update Query Help Please!

[nighthawk999]

I have one table, I want to update multiple fields in that Table with values.

Currently I have on update query for each update.

So for example

Rows: Commercial Quotes, Cover Notes, Personal Quotes

Will have the values: Yes, No, Yes in them.

I need to update those fields, if they say Yes to Commercial Quotes, Cover Notes or Personal Quotes depending on the field obviously. And blank them out if it says No.

Currently I have 30+ different queries I have to click individually one after the other to update each row.

I was wondering if theres a quicker way of doing this all in one hit.

I have a tiny bit of SQL knowledge.

So one of the queries I've figured out I've converted to SQL but again it only updates one instance...

UPDATE Enabler_Data SET Enabler_Data.[Commercial Quotes] = "Commercial Quotes" WHERE (((Enabler_Data.[Commercial Quotes]) = "YES"));

I tried whacking an OR replacing the ; and repeating that statment replacing Commercial Quotes with Cover Notes but it errored out on me.

This is where I get completely stuck and my meagre knowledge fails me so any help would be useful.

Thanks in advance

Matt

 

[Dennisk]

create a procedure
and create a Docmd.OpenQuery for each query.
then keep the cursor positioned in the routine and click run all the queries will now be executed.

or create a form with a button the executes the above routine.

 

[nighthawk999]

I should of added, I have a little SQL knowledge and a little Microsoft Access knowledge and zero knowledge about things like creating procedures etc.

I know the theory just never done it in practice so wouldn't even know where to start.

I'm assuming this is now dipping into advanced stuff? or advanced stuff for someone who knows the visual side of Access and rarely dips into the code

 

[gemma-the-husky]

however, it sounds like you might have normallisation issues

i cant visuallise why you have so many different rows to need updating etc

what is your table structure, and what are you trying to do - i doubt if you should need 30 queries for what sounds like quite a simple exercise

 

[nighthawk999]

The table consists of

User Name
Address
Address 2
Commercial Quotes (which can be a Yes or a no)
Cover Notes (yes or no)
Personal Quotes (yes or no)
And various other things which'll be yes or no.

I can't see how else you'd handle that?

 

[gemma-the-husky]

so you only have one table?

why d oyouv need any queries - just bring up the record you want, and click the appropriate check boxes. what are we missing?

 

[nighthawk999]

The Table has 39,000ish records.

Basically I get a download of data from one source, which needs tidying up to be able to upload into another source.

Some of that 'tidying up' means I have to update fields en masse.

So changing the Commercial Quotes field for example from Yes to Commercial Quotes so the other system accepts it.

I can't change either the original source download data nor the system I'm uploading into hence the reason to tidy up.

Thanks for the responses info hopefully I'm making sense hehe

 

[gemma-the-husky]

then you need an update query

put your table into a query - right click the panel and select query type, update query

now drop the target field into the design grid, in the criteria row put "Yes", or whatever you need to select these rows, and in the update to row, put "2", or whatever you wantvto change them to.

is that what you mean?

 

[nighthawk999]

then you need an update query

put your table into a query - right click the panel and select query type, update query

now drop the target field into the design grid, in the criteria row put "Yes", or whatever you need to select these rows, and in the update to row, put "2", or whatever you wantvto change them to.

is that what you mean?

That's what I already have.

Hence my original question hehe I have about 30 different update queries to clean up various fields for each record so I can then upload.

My question was, is there a way I can do an 'all in one' update query because doing it through access if I use multiple fields it's an 'AND' between each criteria.

So if Commercial Quotes is Yes then update it to commercial quotes, but if I add in Cover notes into this update query..
.
If Cover notes is Yes then it updates to Covernotes only if Commercial quotes is a yes (AND instead of OR)

If I could stick an OR between the two criteria I could do it all through the graphical query tool, however so far I've not figured out if I can or not and it seems SQL statement is the only way to go....

Or just run 30 queries separately one after the other which is what I'm trying to avoid

 

[Brianwarnock]

Put an iif in the update to row
ie IIF([fldname]="yes","text","") then you can do it all in one update.

Brian

 

[gemma-the-husky]

a single update query will fix all your columns at the same time - its the criteria row that determines whether only selected rows are updated

copy your table just in case, but try it

 

[Brianwarnock]

Gemma that is his/her problem. he/she is putting the update criteria in the criteria row, an obvious approach but no good in these cicumstances, he/she needs to do as I stated.

Brian

 

[Rich]

Put an iif in the update to row
ie IIF([fldname]="yes","text","") then you can do it all in one update.

Brian

If fieldname is a boolean then there are no quotes surrounding Yes

 

[Brianwarnock]

If fieldname is a boolean then there are no quotes surrounding Yes

I've assumed it isn't since he wants to replace it with text, can you do that?

Brian

 

[Rich]

I misread it, it must be the storm that never was

 

[nighthawk999]

Put an iif in the update to row
ie IIF([fldname]="yes","text","") then you can do it all in one update.

Brian

Ahh so I can use the GUI query builder? And then in each row I put this expression?

 

[Brianwarnock]

Ahh so I can use the GUI query builder? And then in each row I put this expression?

Hmm, if you mean that can you use the query design grid and put that type of expression in the Updateto cell of each field column, then yes.

Brian

 

[nighthawk999]

Hmm, if you mean that can you use the query design grid and put that type of expression in the Updateto cell of each field column, then yes.

Brian

Yes that's exactly what I meant, because if I do the criteria as just "yes" with update to "Commercial Quotes" then add another one in the next column with criteria as "yes" update to "cover notes"

It treats that as an AND. I really need it to be OR's.

So putting that Expression in the Criteria will solve that presumably? I can just put all the fields I need to update into that one Query with expressions in the criteria and it'll update?

However...won't it still treat them as ANDs?

Beginning to think I'll just have to stick with the 30 queries option

 

[gemma-the-husky]

dont put the new values in the criteria column - thats where youre going wrong

you need to change the query to an update query, and put the new value in the update row

 

[Brianwarnock]

I'm beginning to wonder if the real problem is that he cannot read English.

Brian