Workaround for Nested IF Limits

[sexton1987]

Hi There,

I have created a simple database with the below make up. Link File (Table) to excel spreadsheet which will be updated monthly with a new rolling 12 month forecast. Market intelligence (product code, quantity, country and month) is uploaded into a separate table [Country Load Template]. I then run a query to consolidate the information in [Country Load Template] to group by product code and month. This is to see for each product code by month, what is the total requested quantity. Once I have this information, I then need to calculate the consolidated quantity as a % of the forecast (link excel file) where code and month match.

I've used nested ifs for this but due to limit and having to hard code the dates this is not idea/working. We tried the Case statement but could not get it to work for us. Any help greatly appreciated. Below is nested if code:

SELECT [MI Consolidation Query].Code, [MI Consolidation Query].Qty, [MI Consolidation Query].Mth, IIf([MI Consolidation Query].Mth="009/2015",Nz(Round(([MI Consolidation Query].Qty/[EMEA Forecast].[009/2015]),4)*100),IIf([MI Consolidation Query].Mth="010/2015",Nz(Round(([MI Consolidation Query].Qty/[EMEA Forecast].[010/2015]),4)*100),IIf([MI Consolidation Query].Mth="011/2015",Nz(Round(([MI Consolidation Query].Qty/[EMEA Forecast].[011/2015]),4)*100),IIf([MI Consolidation Query].Mth="012/2015",Nz(Round(([MI Consolidation Query].Qty/[EMEA Forecast].[012/2015]),4)*100),IIf([MI Consolidation Query].Mth="001/2016",Nz(Round(([MI Consolidation Query].Qty/[EMEA Forecast].[001/2016]),4)*100)))))) AS [% OF EMEA Forecast], IIf([% OF EMEA Forecast]<3,"Order",IIf([% OF EMEA Forecast]="#Div/0!","Load","Load Fcst")) AS [Output 1], IIF([Output 1]="Order","Order","Load Forecast")
FROM [MI Consolidation Query] INNER JOIN [EMEA Forecast] ON [MI Consolidation Query].Code = [EMEA Forecast].Parent
WHERE ((([MI Consolidation Query].Code)=[EMEA Forecast].[Parent]));

[Seth Schrock]

In SQL, the Select Case functionality is provided through the Switch() function. So just redoing that one field, you would get

Switch([MI Consolidation Query].Mth = "009/2015", Nz(Round(([MI Consolidation Query].Qty/[EMEA Forecast].[009/2015]),4)*100)
    , [MI Consolidation Query].Mth="010/2015", Nz(Round(([MI Consolidation Query].Qty/[EMEA Forecast].[010/2015]),4)*100)
    , [MI Consolidation Query].Mth="011/2015", Nz(Round(([MI Consolidation Query].Qty/[EMEA Forecast].[011/2015]),4)*100)
    , [MI Consolidation Query].Mth="012/2015", Nz(Round(([MI Consolidation Query].Qty/[EMEA Forecast].[012/2015]),4)*100)
    , [MI Consolidation Query].Mth="001/2016", Nz(Round(([MI Consolidation Query].Qty/[EMEA Forecast].[001/2016]),4)*100)
    ) AS [% OF EMEA Forecast]

While working this out, I noticed that you aren't providing any of your Nz() functions with a value to return if your statements are Null. Also, I'm pretty sure that if your Qty field is null, then your Round function will fail before the Nz() function comes into play.