If Else Ladder in MS Access SQL query

Question

How can I write an If else Ladder in MS Access SQL query and not in the code using Recordset.

I have the conditions like this:

SELECT min(ThresholdValue) FROM tbl_Threshold WHERE SpecialityCode = '130' 
AND SpecialistCode = 'C4535421' AND WeekNum = 1

It will give any value and if not, I want to run this query:

SELECT min(ThresholdValue) FROM tbl_Threshold WHERE SpecialityCode = '130' 
AND SpecialistCode = 'C4535421' AND (WeekNum is Null or WeekNum = '')

It will give any value and if not, I want to run this query:

SELECT min(ThresholdValue) FROM tbl_Threshold WHERE SpecialityCode = '130' 
AND (SpecialistCode is null or SpecialistCode ='') AND WeekNum = 1

It will give any value and if not, I want to run this query:

SELECT min(ThresholdValue) FROM tbl_Threshold WHERE SpecialityCode = '130' 
AND (SpecialistCode is null or SpecialistCode ='') AND (WeekNum is Null or WeekNum = '')

It will give any value and if not, I want to run this query:

SELECT min(ThresholdValue) FROM tbl_Threshold WHERE (SpecialityCode is null 
or SpecialityCode ='') AND (SpecialistCode is null or SpecialistCode ='') AND WeekNum = 1

It will give any value and if not, I want to run this query:

SELECT min(ThresholdValue) FROM tbl_Threshold WHERE (SpecialityCode is null 
or SpecialityCode ='') AND (SpecialistCode is null or SpecialistCode ='')      
AND (WeekNum is Null or WeekNum = '')

Basically, I am giving 3 conditions in the query, and if no record found for that condition, then I am replacing a condition with blank value or null step by step. Currently I am doing it using recordsets, means checking count of a recordset, if 0 then filling recordset with next condition and so on. But this approach is taking so much time, because this itself is being done in a loop over 100 around records. So any other way I could do this,may be in a single query?

Could you explain in layman's term what is that you are trying to do? There must be an other way of doing this, unlike testing multiple conditions so many times. — PaulFrancis, Jun 18 '15 at 08:36
@PaulFrancis I have already explained it in simple words at the bottom of my thread. First I am checking if there is a row in table with all 3 conditions, if not then I am ignoring third condition at a time and then checking on base of rest 2 conditions, if then also data is not found, then I am ignoring second condition and checking only on first condition. Because there might be data with values in all these 3 columns or not. — Hemant Sisodia, Jun 18 '15 at 08:47
To provide an alternative I think we need to know the purpose of this approach to begin with. — Newd, Jun 18 '15 at 12:38

score 0 · Answer 1 · answered Jun 21 '15 at 13:36

Consider a nested IIF statement. Notice the inverted hierarchy of conditions:

SELECT min(IIF((SpecialityCode is null or SpecialityCode ='') AND (SpecialistCode is null or SpecialistCode ='') AND (WeekNum is Null or WeekNum = ''), 
ThreshodValue,
IIF((SpecialityCode is null or SpecialityCode ='') AND (SpecialistCode is null or SpecialistCode ='') AND (WeekNum = 1),
ThresholdValue,
IIF(SpecialityCode = '130' AND (SpecialistCode is null or SpecialistCode ='') AND (WeekNum is Null or WeekNum = ''),
ThresholdValue,
IIF(SpecialityCode = '130' AND (SpecialistCode is null or SpecialistCode ='') AND WeekNum = 1, 
ThresholdValue,
IIF(SpecialityCode = '130' AND SpecialistCode = 'C4535421' AND (WeekNum is Null or WeekNum = ''), 
ThresholdValue,
IIF(SpecialityCode = '130' AND SpecialistCode = 'C4535421' AND WeekNum = 1, 
ThresholdValue, 0)))))))
FROM tbl_threshold;

Alternatively, you can consider a Union query where if select statements that have no value will be excluded and union (vs union all) prevents duplicates. But possibly you want to return ids and thresholdvalues in union query and aggregate union query with min() in another query.

SELECT min(ThresholdValue) FROM tbl_Threshold WHERE SpecialityCode = '130' AND SpecialistCode = 'C4535421' AND WeekNum = 1
UNION
SELECT min(ThresholdValue) FROM tbl_Threshold WHERE SpecialityCode = '130' AND SpecialistCode = 'C4535421' AND (WeekNum is Null or WeekNum = '')
UNION
SELECT min(ThresholdValue) FROM tbl_Threshold WHERE SpecialityCode = '130' AND (SpecialistCode is null or SpecialistCode ='') AND WeekNum = 1
UNION
SELECT min(ThresholdValue) FROM tbl_Threshold WHERE SpecialityCode = '130' AND (SpecialistCode is null or SpecialistCode ='') AND (WeekNum is Null or WeekNum = '')
UNION
SELECT min(ThresholdValue) FROM tbl_Threshold WHERE (SpecialityCode is null or SpecialityCode ='') AND (SpecialistCode is null or SpecialistCode ='') AND WeekNum = 1
UNION
SELECT min(ThresholdValue) FROM tbl_Threshold WHERE (SpecialityCode is null or SpecialityCode ='') AND (SpecialistCode is null or SpecialistCode ='') AND (WeekNum is Null or WeekNum = '');

If Else Ladder in MS Access SQL query

1 Answers1