Ab-normalization at Experts Exchange - part 15

Question today: formula in access to replace values in a column

The problem:
Each column corresponds to a question number and for each question number one of the values (1,2,3,4,5) represents the correct

answer for the whole column.

For example in the column 'Q1', the value '4' is the correct answer for all the records in the column.

For column 'Q1' I would like all the '4' values to be replaced by the value '1' and the rest of the values (1,2,3,5) to be

replaced by the value '0'


The crappy solution:
update Table1 set Q1=iif([Q1]="4","1","0")

Obviously capricorn1 doesn't know how a SELECT query could do this without "deleting" the original value.
The general approach to this is to leave the original table "as is" and to define a query for repacing the value.

As expected, this is a "bridge too far" for this "would be" expert.
He even has to apologize for not reading the question correctly as he overlooked Q2, Q3, etc.

I could also tell the questioner that his table isn't normalized and that using a normalized "question" table and "answer" table

would make a really flexible way to construct a questionair.
But as capricorn1 is only interested in points, this won't be noticed by him....

Nic;o)
Still proud to be suspended from this "quality site" where wannabee experts rule and "quality" is just another word from the

dictionary.
(The question can be found at: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-

Macros/Q_24533410.html)