var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Query question
I am new to the database world and I have two questions. I am using MS Access
1) I have a table (Say table1) which has 2 columns (Col1,Col2). Col3 is
generated on fly by using an expression based on col1 and col2 like
(Col1/Col2) AS Col3 in the select statement. Some of the values in Col1,Col2
make calaculated value to become infinity eg Col2 is zero. I just want to
know if I can use any conditional statement so that error won't occur, i.e.
if the value of col2 is zero then calculated value should result in zero.
2) I also want to filter on calculated column col3. I want to say in my where
clause show only those results where Col3 > 10.
I suspect that I cannot use like this "Select Col1,Col2,(Col1/Col2) AS Col3
from table1 where Col3 > 10" . If I use like this then MS Access ask to enter value for Col3
Thanks in advance
iif might be appropriate
if you are using NULL the nz function may be appropriate
set the iif statement as the source for your derived column
Col3: iif([col1]=0 OR [col2]=0,0,[Col1]/[Col2])
Thanks for the quick reply. I will try this iif statement. Can I filter on Col3 also ??
Actually, healdem wasn't talking SQL-ese, he was talking Access Query-ese. In SQL, the code would be
SELECT Col1, Col2, IIf(([Col1]=0) OR ([Col2]=0),0,[Col1]/[Col2]) As Col3 WHERE IIf(([Col1]=0) OR ([Col2]=0),0,[Col1]/[Col2])>10
As Sam helpfully points out the iif example was geared to someone using the Access query designer, rather than someone rolling their own SQL. The query designer helpfully repeats the iif as part of the criteria, thus avoiding typos.
IMHV It flattens ever so slightly the steepness of the learning curve in desiging queries. Its also quite a good tool for seeing how SQL could vbe written, especially if, as and when you start doing complex joins and selection criteria.
Since Col1 and Col2 are also calculated columns ( ie Col1 --> Max(another col), I have trouble using the above query
Then you need to be explicit and include the calculations as opposed to the field names:
Select MAX(someCol) As Col1,Col2,(MAX(someCol)/Col2) AS Col3