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]));
1 1887
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Joshua Beall |
last post by:
Hi All,
I remember reading that both nested classes and namespaces would be
available in PHP5. I know that namespaces got canceled (much sadness...),
however, I *thought* that nested classes were still an option.
However, I am coming up dry looking for information on how to do this, and
the most recent references I am able to find to nested classes in PHP are
dated 2003. And they all say the same thing: sorry, can't do that.
|
by: platho |
last post by:
Hello,
I bounced into the max 25 columns index limits on DB2 v7.2 on NT.
Is this still so in other operating systems or in v8 ? Are there plans to
change this in the future ? Any workarounds or hints or tips? I think
Oracle allows 30 fields in an index ...
Thank you very much for an answer
Regards
|
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I COULD be wrong... :) I've tried the
access group...twice...and all I get is "Access doesn't like ".", which I know,
or that my query names are too long, as there's a limit to the length of the SQL
statement(s). But this works when I don't try to...
|
by: Neptune |
last post by:
Hello.
I am working my way through Zhang's "Teach yourself C in 24 hrs (2e)"
(Sam's series), and for nested loops, he writes (p116) "It's often
necessary to create a loop even when you are already in a loop." Then he
goes on to portray a contrived example that doesn't tell me under what
conditions a nested loop might be favoured as a solution? i.e. what are
nested loops useful for? What kinds of algorithms are served by nested
loops?...
|
by: Profetas |
last post by:
Hi,
Nested statements helps to generate complex source code,
what is the nesting limit that you normally consider?
I know that the Halsted and McCabe analyses can define the acceptable
level, but in your personnel point of views what is the limit before you
re-code the block into multiple blocks
a 3 level nesting example
| |
by: GY2 |
last post by:
I writing some documentation and I want to describe a common code structure
which is used to step through all the items in a collection (e.g. each file
in a subdirectory) while applying more and more restrictive filters so that
only the desired items can fall all the way through. This method is so
obvious and common it must have a name. What is it or at least, what is the
best (short) way to describe it?
For Each In
If Then
If Then
|
by: Fredrik Lundh |
last post by:
Patrol Sun wrote:
so why exactly are you trying to nest 20 or 100 for-in loops?
</F>
|
by: vippstar |
last post by:
n1256.pdf (C99 TC3), 5.2.4.1 Translation limits p1 says:
Does that mean that *any* program using an array of two or more
elements other than char is allowed to be rejected by an
implementation?
For example,
int main(void) { int array; return 0; }
|
by: Nathan Sokalski |
last post by:
I have several nested For loops, as follows:
For a As Integer = 0 To 255
For b As Integer = 0 To 255
For c As Integer = 0 To 255
If <Boolean ExpressionThen <My CodeElse Exit For
Next
If Not <Boolean ExpressionThen Exit For
Next
If Not <Boolean ExpressionThen Exit For
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |