How do you get SQL to add a variable to a field name in a where clause?

Question

I am working with MS-Access. I have a table I am modifying, the fields of the table are:

NumberOfCycles, s1,s2,s3,s4...s8

the rows have different numbers of s's in them, some have all eight and some have less (the number of s's is specified by NumberOfCycles). The way the table is now I have redundant data so I'll have something like this.

NumberOfCycles, s1,s2,s3,s4...s8  
       4        1   0  1  0  
       4        0   1  0  
       4        1   0  
       4        0  

I only want the first row. I tried to solve this by adding a statement to my where clause:

where 's' + NumberOfCycles <> Null

This doesn't work because SQL just compares the string 's4' with null. How can I have it so it compares the value of the field s4 (or whatever value of NumberOfCycles) with Null?

Answer 1

WHERE CASE WHEN NumberOfCycles = 7 THEN s7 
           WHEN NumberOfCycles = 6 THEN s6 
           ...
      END IS NOT NULL

Never test for <> NULL. Even NULL is not equal to NULL, and so that always returns true.

Answer 2

Standard SQL:

WHERE
     CASE NumberOfCycles
          WHEN 1 THEN s1
          WHEN 2 THEN s2
          WHEN 3 THEN s3
          WHEN 4 THEN s4
          WHEN 5 THEN s5
          WHEN 6 THEN s6
          WHEN 7 THEN s7
          WHEN 8 THEN s8
          ELSE NULL
     END IS NOT NULL  -- Can't use <> for NULL

For MS Access I believe that this would be:

WHERE
     SWITCH(NumberOfCycles=1, s1, NumberOfCycles=2, s2, NumberOfCycles=3, s3...) IS NOT NULL

Answer 3

I agre with a comment, you should review the design for alternatives.

In terms of the existing design there appear to be two options to me...
1. Build up the query string in VBA, then execute it
2. Use a CASE statement in the WHERE clause

I admit I don't know Access's syntax, so here is SQL Server syntax for your inspiration...

WHERE
   CASE NumberOfCycles
      WHEN 1 THEN s1
      WHEN 2 THEN s2
      WHEN 3 THEN s3
      WHEN 4 THEN s4
   END
   IS NOT NULL

This, however, is not efficient as it is scanning every record and not able to make use of indexes, etc.

EDIT

Also, note that based on your example and actual question, if you ever have "NumberOfCycles = 3" but records where s3 and s4 are populated, both records are returned.

I believe you would be advised to have a field "cycle" which describes the cycle at which the data was populated.

   Cycle | Number Of Cycles | s1 | s2 | s3 | s4
      1  |                4 |  0 |  - |  - |  -
      2  |                4 |  0 |  1 |  - |  -
      3  |                4 |  0 |  1 |  0 |  -
      4  |                4 |  0 |  1 |  0 |  1

Then you just need to search for "Cycle = NumberOfCycles"

EDIT

Apparently Access has a horrible SWITCH statement...

WHERE
   SWITCH(
      NumberOfCycles=1, s1,
      NumberOfCycles=2, s2,
      NumberOfCycles=3, s3,
      NumberOfCycles=4, s4
   )
   IS NOT NULL

Or you can expand to a horrible OR statement...

WHERE
   (NumberOfCycles=1 AND S1 IS NOT NULL)
OR (NumberOfCycles=2 AND S2 IS NOT NULL)
OR (NumberOfCycles=3 AND S3 IS NOT NULL)
OR (NumberOfCycles=4 AND S4 IS NOT NULL)

Answer 4

A rough example using a Union query.

SELECT a.Cycle, a.NoCycles, a.CS, a.CSName 
FROM
    (SELECT c.Cycle, c.NoCycles, c.s1 As CS, "s1" As CSName
    FROM CycleTable c
    UNION ALL
    SELECT c.Cycle, c.NoCycles, c.s2 As CS, "s2" As CSName
    FROM CycleTable c
    UNION ALL
    SELECT c.Cycle, c.NoCycles, c.s3 As CS, "s3" As CSName
    FROM CycleTable c
    UNION ALL
    SELECT c.Cycle, c.NoCycles, c.s4 As CS, "s4" As CSName
    FROM CycleTable c
    UNION ALL
    SELECT c.Cycle, c.NoCycles, c.s5 As CS, "s5" As CSName
    FROM CycleTable c
    UNION ALL
    SELECT c.Cycle, c.NoCycles, c.s6 As CS, "s6" As CSName
    FROM CycleTable c
    UNION ALL
    SELECT c.Cycle, c.NoCycles, c.s7 As CS, "s7" As CSName
    FROM CycleTable c
    UNION ALL
    SELECT c.Cycle, c.NoCycles, c.s8 As CS, "s8" As CSName
    FROM CycleTable c) a

WHERE a.Cycle=4 AND a.CSName="s4"