Nested IF Workaround? (1 Viewer)

sexton1987

New member
Local time
Today, 11:52
Joined
Aug 6, 2015
Messages
1
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

The Mailman - AWF VIP
Local time
Today, 12:52
Joined
Aug 11, 2003
Messages
11,696
try posting readable sql:
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]));
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

Registered User.
Local time
Today, 17:22
Joined
Mar 31, 2016
Messages
19
It is possible to circumvent limitation over the number of nested conditional formulas by cascading them (7 nests).
 

Users who are viewing this thread

Top Bottom