Nested IF Workaround?

[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]));

 

[namliam]

try posting readable sql:

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]));

dates or multiple values that are the same, in a database should be stored as rows not columns.

Suggest you fix your data upon import instead of trying to work with a broken spreadsheet design inside a database.

 

[yupstrips01]

It is possible to circumvent limitation over the number of nested conditional formulas by cascading them (7 nests).