Here is the query. Basically we want to duplicate the large set of IIf
statements, this time only displaying a result if the ACNMonthNumber is
between or equal to the Start Date and End Date.
SELECT tblWeeklyPipeLine.SAPOMID,
tblOpportunity.OppName,
tblWeeklyPipeLine.Stage,
tblWeeklyPipeLine.SignDate,
tblWeeklyPipeLine.TotalValue,
tblWeeklyPipeLine.WinRate,
[TotalValue]*([WinRate]/100) AS WeightedValue,
tblWeeklyPipeLine.CIRate,
[TotalValue]*[CIRate]/100 AS CIValue,
tblMonth.QID,
tblMonth.MonthAbr,
tblMonth.ACNMonthNum,
IIf([tblWeeklyPipeline].[Tow]="C",[tblWeeklyPipeline].[ConsStartDate],[tblWeeklyPipeline].[OSStartDate]) AS [Start Date],
IIf([tblWeeklyPipeline].[Tow]="C",[tblWeeklyPipeline].[ConsEndDate],[tblWeeklyPipeline].[OSEndDate]) AS [End Date],
tblWeeklyPipeLine.ToW,
tblOpportunity.GroupDeal,
[End Date]-[Start Date] AS [Time Span],
IIf([PRM]<=1,IIf([GroupDeal],IIf([ACNMonthNum]<=1,[WeightedValue]/(12-([PRM]-1))),IIf([ACNMonthNum]=1,[WeightedValue]))) AS [Q1 Sep Sales],
IIf([PRM]<=2,IIf([GroupDeal],IIf([ACNMonthNum]<=2,[WeightedValue]/(12-([PRM]-1))),IIf([ACNMonthNum]=2,[WeightedValue]))) AS [Q1 Oct Sales],
IIf([PRM]<=3,IIf([GroupDeal],IIf([ACNMonthNum]<=3,[WeightedValue]/(12-([PRM]-1))),IIf([ACNMonthNum]=3,[WeightedValue]))) AS [Q1 Nov Sales],
IIf([PRM]<=4,IIf([GroupDeal],IIf([ACNMonthNum]<=4,[WeightedValue]/(12-([PRM]-1))),IIf([ACNMonthNum]=4,[WeightedValue]))) AS [Q2 Dec Sales],
IIf([PRM]<=5,IIf([GroupDeal],IIf([ACNMonthNum]<=5,[WeightedValue]/(12-([PRM]-1))),IIf([ACNMonthNum]=5,[WeightedValue]))) AS [Q2 Jan Sales],
IIf([PRM]<=6,IIf([GroupDeal],IIf([ACNMonthNum]<=6,[WeightedValue]/(12-([PRM]-1))),IIf([ACNMonthNum]=6,[WeightedValue]))) AS [Q2 Feb Sales],
IIf([PRM]<=7,IIf([GroupDeal],IIf([ACNMonthNum]<=7,[WeightedValue]/(12-([PRM]-1))),IIf([ACNMonthNum]=7,[WeightedValue]))) AS [Q3 Mar Sales],
IIf([PRM]<=8,IIf([GroupDeal],IIf([ACNMonthNum]<=8,[WeightedValue]/(12-([PRM]-1))),IIf([ACNMonthNum]=8,[WeightedValue]))) AS [Q3 Apr Sales],
IIf([PRM]<=9,IIf([GroupDeal],IIf([ACNMonthNum]<=9,[WeightedValue]/(12-([PRM]-1))),IIf([ACNMonthNum]=9,[WeightedValue]))) AS [Q3 May Sales],
IIf([PRM]<=10,IIf([GroupDeal],IIf([ACNMonthNum]<=10,[WeightedValue]/(12-([PRM]-1))),IIf([ACNMonthNum]=10,[WeightedValue]))) AS [Q4 June Sales],
IIf([PRM]<=11,IIf([GroupDeal],IIf([ACNMonthNum]<=11,[WeightedValue]/(12-([PRM]-1))),IIf([ACNMonthNum]=11,[WeightedValue]))) AS [Q4 July Sales],
IIf([PRM]<=12,IIf([GroupDeal],IIf([ACNMonthNum]<=12,[WeightedValue]/(12-([PRM]-1))),IIf([ACNMonthNum]=12,[WeightedValue]))) AS [Q4 Aug Sales]
FROM tblMonth, tblOpportunity INNER JOIN tblWeeklyPipeLine ON
tblOpportunity.SAPOMID = tblWeeklyPipeLine.SAPOMID
WHERE (((Month([tblWeeklyPipeline].[SignDate]))=[tblMonth].[MonthID]));
KARL DEWEY said:
Post the complete query SQL.
--
KARL DEWEY
Build a little - Test a little
Nick said:
Yeah, I understand that, it is a check box. It reads that just fine.
What my question is, how can I make the column populate only if the month
the column represents (statically assigned) is in the range that the contract
is in. So say the contract goes from July - October. I don't want the
expression to return any value for any months outside of that range (i.e.
Nov, Dec, Jan, Feb, Mar, Apr, May, June).
:
It is according to what you have stored in [GroupDeal] as to how to
evaluated.
If it is a Yes/No field the like this ---
Expr1: IIf([GroupDeal] = -1,[WeightedValue]/([Time Span (Months)]),
IIf((Month([tblWeeklyPipeline]![SignDate])=9), [WeightedValue]))
Or if it is a text field with words 'Yes' or 'No' then like this ---
Expr1: IIf([GroupDeal] = "Yes",[WeightedValue]/([Time Span (Months)]),
IIf((Month([tblWeeklyPipeline]![SignDate])=9), [WeightedValue]))
--
KARL DEWEY
Build a little - Test a little
:
Hi,
My expression:
Expr1: IIf([GroupDeal],[WeightedValue]/([Time Span
(Months)]),IIf((Month([tblWeeklyPipeline]![SignDate])=9), [WeightedValue]))
It basically says if it is a group deal, then populate with the weighted
value divided by the number of months of the project. If it is not a group
deal and if it is in September, the populate with the entire weighted value.
My question is, how do I contain the first part, if it is a group deal, to
just the months the project is running (I have the dates in columns called
Start Date and End Date). For example, if I have a $100 project that runs
from April to July, I want $25 shown in each of the four months, April, May,
June, and July. As the expression stands now, it divides by four to get the
$25, but then it will put $25 in for all twelve months.
Thanks!
