Multiple Fields One Update Query

[rpadminserver]

Hello all

Is it possible to do multiple updates in one query.

This is a example of what I'm trying to do:

******
UPDATE TEST4a set

(TEST4a.NNd = replace(Replace(Replace(TEST4a.NNd,"A",""),"V",""),"..",".")
WHERE TEST4a.NND is not null and test.nnd <> "N/A" ) and

(TEST4a.NNP = replace(Replace(Replace(TEST4a.NNp,"A",""),"V",""),"..",".")
WHERE TEST4a.NNp is not null and test.nnp <> "N/A")
******

There are some 20 fields that I need to make this check on. I did not want to clutter my query library wtih that many queries.


Thanks!

FYI.. I'm using access 2003

 

[jackd]

No that syntax isn't correct.
I think you're trying to update several fields in the same table, and that is doable, but you can't have multiple WHERE clauses.

The general syntax would be along this line

Update MyTable
SET
TEST4a.NNd = replace(Replace(Replace(TEST4a.NNd,"A",""),"V",""),"..",".")
,TEST4a.NNP = replace(Replace(Replace(TEST4a.NNp,"A",""),"V",""),"..",".")
,nextfld
WHERE
(TEST4a.NND is not null and test.nnd <> "N/A" ) and
(TEST4a.NNp is not null and test.nnp <> "N/A" ) and .....

 

[rpadminserver]

Jack...
Thanks a bunch!

I could not get the "and" or the "OR" to work right in the where statement. Either I was updating all the records in my table or none of the records.

Since one of my replace statements is to replace "A" with blank, all my "N/A" became "N/"
So I added another replace to change "N/" to "N/A" and then I was able to remove the WHERE statements all together.

Might not be proper programming, but it gets the job done!
Thanks again.

 

[Joe4]

Yes, by doing it all in a single query, it will update ALL the fields you have listed in your SET statements for the group of records selected by your criteria.

If they all have the same criteria, and you want to update all those fields for every record, it is pretty easy and straighforward. If not, you need to either break it up into multiple queries or be a bit more careful with the SET code (maybe use some IIF statements to determine when to update which fields).

A bit more advanced approach would be to use RecordSet logic in VBA and loop through the recordset and update the fields that meet your criteria. Then it can all be done in a single process (though this is a bit more complex and requires some knowledge of VBA and Recordsets).

 

[rpadminserver]

Thanks for info Joe!

I would love to use VBA.
I'm only a contract worker and after I'm done on this project only SQL people will be around to make changes. So I'm under orders not to use VBA except for my MENU form that kicks off the queries. You have no idea the hoops I've jumped through to do things in SQL that would be SO MUCH EASIER to do in VBA.

Again, thanks for the feed back
R

 

[Joe4]

I would love to use VBA.
I'm only a contract worker and after I'm done on this project only SQL people will be around to make changes

Understood.

One option would be to use multiple queries, then just create a Macro (not the same as VBA in Access - use the Macro object) to run all those queries with a single click.

The big advantage to that is if you are turning it over to someone else, it may make it a little easier for them to maintain/support (because if you tried to do it all in a single query, it could get quite complex and hard to follow). You probably know how much fun it can be to try to debug or reverse engineer someone else's handiwork!