MS Access SQL to add a new column which is a score from other columns

Question

I currently have an MS Access table consisting of 20 columns. I need to add a new column which is a score, 0 - 5, based on values in other columns

NewScore is defaulted to 0
if column1 is between 11 and 20 then NewScore is NewScore + 1
if column2 is between 7 and 10 then NewScore is NewScore + 1
if column3 is between 1 and 5 then NewScore is NewScore + 1
if column4 is between 1 and 20 then NewScore is NewScore + 1
if column5 is between 51 and 80 then NewScore is NewScore + 1

I have accomplished the task using multiple queries and then a final query issuing a COUNT but there must be a cleaner more efficient method.

Thanks for your help

Answer 1

Use switch:

select switch(column1 between 11 and 20, NewScore + 1,
              column2 between 7 and 10, NewScore + 1,
              . . . 
              1=1, NewScore
             ) as NewScore

It occurs to me that you might actually want these added together. If so:

select (iif(column1 between 11 and 20, 1, 0) +
        iif(column2 between 7 and 10, 1, 0) +
              . . . 
       ) as NewScore