I would like to create a new column in my query that does a conditional calculation across several variables in the table used for the query. If it is easier or more direct, I could also do this in the table within the database.
A simple example of what I am trying to do would have a TestTable with the entries of:
TableID, Primary Key, AutoValue
Value1, Value2, and Value 3 all Integer (Long)
Within the test table, there are 7 entries, with Value1, Value2 and Value3 ranging from 1 to 9 and if no entry was made, it shows up as a 0.
I would like to calculate the number of non-zero entries across the variables Value1, Value2 and Value3 for every case and enter that in a new column.
I tried to add a formula to the table, but haven’t discovered how to do that, so I turned to Query and find that if I go in GUI I can add my test table and in Field I can simply type in Value1
/Value1
and that becomes a new column with a value of 1 if the entry is something other than 0, or 0, if it was a 0. This seemed promising, for I could then create 2 more columns Value2
/Value2
and Value3
/Value3
. Then I could Write in an Alias for each one and create 4th column Alias1
+Alias2
+ Alias3
and if all 3 values were non-zero, then I would get a 3, but if any one was a 0, I would get a 0, so this didn’t give me the values I wanted.
In investigating SQL options, it looked like the CASE option could do what I wanted. Something like
SELECT TableID
, Value1
, Value2
, Value3
,
N1
= CASE
WHEN Value1
>0 THEN 1 ELSE 0
END
FROM TestTable;
This would have to be repeated for each value and then, if the 0’s created this way were really 0’s that could be part of another calculation, then N1+N2+N3 would yield the value I want. But I can’t even get past a single SQL entry this way without the same Syntax error.
Any Idea on how to do this simple calculation?
Larry