Optional Access column value selection

Question

I have an Access table that looks like this:

A | B | C | Final
1 |   |   | 1
2 | 3 |   | 
4 | 4 |   | 4
5 | 5 | 5 | 5
  | 4 | 1 | 
9 | 2 | 4 |

The values with an asterisk below are the goal. I need a script that can choose between n-columns that recognizes a precedence rule which in this case is A > B > C in case of value duplication.

A | B | C | Final
1 |   |   | 1
2 | 3 |   | *2
4 | 4 |   | 4
5 | 5 | 5 | 5
  | 4 | 1 | *4
9 | 2 | 4 | *9

So far I've achieved the other situations in this topic, which is in Case 2 but I have 65000+ items wherein manual selection of the values would take a load of time. I am hoping for an automated script that can do this in Access.

So far I can't implement this pseudocode:

SELECT ID, A, B, C FROM table

// loop per row
if ( countUniqueValues(A, B, C) >= 2 ) 
then
   if ( hasValue(A) )
       then Final = A.value
   else if ( hasValue(B) )
       then Final = B.value
   else Final = C.value

because the one below I'm working on is still flawed:

SELECT ID, N1 &
  // I want to compare each value with NOT NULL and pick based on the pseudocode
  IIf(N1 NOT NULL) AS Final OR
  IIf(N2 NOT NULL) AS Final OR
  IIf(N3 NOT NULL) AS Final
FROM
  (SELECT ID, Nz(A) AS N1, Nz(B) AS N2, Nz(C) AS N3
   FROM table) AS T
ORDER BY ID;

Based on what you learned from the previous question, how do you think you *might* go about it? Edit your question to show us what you've tried so far. — Gord Thompson, Nov 24 '13 at 09:38

score 1 · Accepted Answer · answered Nov 24 '13 at 11:08

1

You can use the SWITCH function:

SELECT  A,
        B,
        C,
        SWITCH
        (   A IS NOT NULL, A,
            B IS NOT NULL, B,
            C IS NOT NULL, C
        ) AS Final
FROM    T;

answered Nov 24 '13 at 11:08

GarethD

68,045
10
83
123

Optional Access column value selection

1 Answers1