One Query to update Two Fields ?? (1 Viewer)

[indyaries]

Greetings,

I'm updating someone else's database using Access 97. I want to replace multiple queries that are being used with just one if possible. These queries are run every month on new Budget data. All fields are in the same Table

Example: Query 1
Field Name: TA
Field Type: Text

Currently using an Update Query to change TA = 41 to TA = 40.


Example: Query 2
Field Name: EOE
Field Type: Text
Currently using an Update Query to change all occurances of 4110 to 4610.


I've searched in the Query Archives, but have found nothing that works.

There are 24,712 records so far in the database. If I use a Select Query (Query 1), there are 24,712 records returned--since all TA's initially are 41. But, when I try to also add criteria to the EOE column, only 14 records are returned. These 14 records are the same as if I only selected all records with an EOE of 4110.

Hence my dilema!! I would like to use ONE query to not only change ALL TA records that =41 to TA=40, and also change all EOE records that =4110 to EOE=4610.

Thank you all in advance!!!!!

Bob in Indy (at work now)

 

[RichMorrison]

Bob,

I think you are stuck.

You have created a query with 2 criteria in an "AND" relationship. So only 14 records are updated since only 14 are EOE=4110.

You could change to an "OR" relationship but that would produce incorrect results if you have records that are not TA=40 or not EOE=4110.

RichM

 

[The_Doc_Man]

Flitting through my mind is the idea that you wanted to do this with a single query.

I agree you can't do this with a single query. Takes at least two.

But...

You could control it with a single macro that has two steps, both of which are OpenQuery steps.

Is this a one-shot problem? Do two queries.

Is this a problem you will address many times? Do two queries and then control them with a single macro.

 

[indyaries]

Alas, I had to try...

Rich and Doc Man,

Thank you for your replies. In fact, there already exists a two-query and macro setup for this particular problem. Was hoping for a way to perform the task with just one.

Well, at least I can get rid of 16 of the current 18 queries that are now being used, as I've decided to use only one data file, instead of the nine that they have been using. Also replaced the ten queries that were being used to change text those strange text number values (positive and negative) with just one query.

Win some, loose some....but this forum is a godsend. Again, thank you both !!!!!

Regards,

Bob in Indy

 

[Jon K]

You can use iif() in your Update Query:-

UPDATE yourTable SET TA = iif(TA="41","40",TA),
EOE = iif(EOE="4110","4610",EOE)
WHERE TA="41" or EOE="4110";

 

[indyaries]

Worked like a charm !!

Jon,

Your SQL worked like a charm. Many thanks to you. Sure will make the database easier to manage.

Thanks to all who replied!

Bob in Indy

 

[RichMorrison]

Good one Jon. I did not think of using IIf. That is a good one to remember.

Thanks.