Replace Value with IFF Function

[CryptikFox]

Hello,


I am wondering what the best way to go about automating some values that need replacing in an access database.


We have a table with over 50,000 entries, and dozens of parameters (columns). Three of them are important here, let's call them the A, B, and C columns.
Columns A and B are all number values. Column C occasionally has a short text value for some rows, occasionally, the singular letter "U."
For a given row, we would like to replace the value in column A, with the value in column B, IF AND ONLY IF the value in column C is "U."




Would this have to be run as a query, or is there a way to make the original table itself do this? Either solution would work for us, but it'd be best if we could have one table with the original data, and a second table that is automatically generated with the replaced values.

Thanks in advance for the help.

[ssanfu]

This could be done with a query (faster) or VBA - it is your choice.

To duplicate the table (make a back up), right click on the table name and select "Copy". Then right click and select "Paste".

Your query would looks something like:

UPDATE Table1 SET Table1.ColA =  Table1.ColB
WHERE Table1.ColC ="U";

Change names as appropriate.....


VBA would take longer because you would have to step through each record (50,000) of the table.


Warning: Make a back up of the database BEFORE trying this!!!! You have been warned......

[Gicu]

Or you could leave your original data in the table and create a new SELECT query in which you use IIF in the A and B columns: NEW_A:iif([C]="U","New_A_Value",[A]).

Cheers,
Vlad

[CryptikFox]

Hey ssanfu, I tried:

UPDATE Table1 SET Table1.ColA =  Table1.ColB
WHERE Table1.ColC ="U";

But keep getting a syntax "error (missing operator)."



Bear with me... I'm a total noob when it comes to any sort of coding or advanced usage of Access.

To clairify, my Columns A, B, and C are as follows:
A=Result
B=PQL
C=Qualifier

I want the "Result" value to be replaced with the "PQL" value if the "Qualifier" value is "U."

Thanks again for the help.

[CryptikFox]

Or you could leave your original data in the table and create a new SELECT query in which you use IIF in the A and B columns: NEW_A:iif([C]="U","New_A_Value",[A]).

Cheers,
Vlad

Hey Vlad, I actually got your method to work... BUT we'd be running into another big issue if we did it this way. The problem is we have a bunch of other macros and queries that rely on the A column to be called A. With your method, the new A column needs to have a different name, otherwise there's a circular reference.

I suppose the query can be copy pasted then "A_New" can be manually renamed back to "A," but that starts defeating the purpose of the automation. Let me know if you have any thoughts on this, and thanks for the help.

[June7]

Can't have those SELECT and UPDATE statements in the same query object.

UPDATE action would make a permanent change of values in field. Do you really want that?

Might just have to do the IIf replacement calc in those other macros and queries. How many is a 'bunch'?

[ssanfu]

Be aware that "Data" and "Result" are reserved words and shouldn't be used as object names.

Here is a dB to demonstrate what you want to do.
Open the table "Data" and look at the values.
Close the table, then execute the query.
Open the table again and check the field values.

[Gicu]

If you fully qualify the name for the fields in the expression you don't need to assign a new name to the field; have a look at the attached sample and you see that I renamed the original table tblResults_Orig and created a new query called tblResults that has the calculated field. In most cases the new query can be used exactly like the table it replaces, including code (so in VBA set rst=currentdb.openrecordset("tblResults", dbOpendynaset) would still work as expected).

I'm not saying it is the best approach from a db design point of view but sometimes we need to take shortcuts....

Cheers,
Vlad