dynamically change the calculation from Sum to Average

[Guest]

Hi. I have a query with three tables. Two of the three columns are shown
below, the third being similar to the second, so I'll leave it out:

1.PlanTypeID
2.MTD:
Val(Nz(Sum(Abs(([DateMonth].[MonthID]=DatePart('m',[Forms]![Dashboard]![Yesterday])
And [YearID]=DatePart('yyyy',[Forms]![Dashboard]![Yesterday])))*[Plan]),0))

I'm trying to dynamically change the calculation from Sum to Average.

PlanTypeID tells me whether the second column should be averaged or summed.
There's a table in the query called PlanType that looks like this:

PlanTypeID Calculation
1 SUM
2 SUM
3 AVG
4 AVG
5 AVG

When the PlanTypeID is 1 or 2, I'd like the equation in the MTD column to
use the SUM function. When it's 3,4 or 5, I'd like it to use the average
function. My guess is I need something like:

2.MTD: Val(Nz( " & PlanType.[Calculation] & "
(Abs(([DateMonth].[MonthID]=DatePart('m',[Forms]![Dashboard]![Yesterday]) And
[YearID]=DatePart('yyyy',[Forms]![Dashboard]![Yesterday])))*[Plan]),0))

Any suggestions would be tremendous. Thanks!

The whole code is below in case you would like to see it:
SELECT PlanProductH2.ProductH2ID, PlanType.PlanTypeID,
Val(Nz(Sum(Abs(([DateMonth].[MonthID]=DatePart('m',[Forms]![Dashboard]![Yesterday])
And [YearID]=DatePart('yyyy',[Forms]![Dashboard]![Yesterday])))*[Plan]),0))
AS MTD,
Val(Nz(Sum(Abs(([DateMonth].[QuarterID]=DatePart('q',[Forms]![Dashboard]![Yesterday])
And [YearID]=DatePart('yyyy',[Forms]![Dashboard]![Yesterday])))*[Plan]),0))
AS QTD
FROM (PlanProductH2 INNER JOIN PlanType ON PlanProductH2.PlanTypeID =
PlanType.PlanTypeID) INNER JOIN DateMonth ON PlanProductH2.MonthID =
DateMonth.MonthID
GROUP BY PlanProductH2.ProductH2ID, PlanType.PlanTypeID;

 

[David S via AccessMonster.com]

Hi Mike,

My recommendation would be to create two additional fields, MTDsum and MTDavg,
the first with your existing formula and the next with the average version of
the same thing. Then, your MTD column can be:

iif(PlanTypeID = "SUM", [MTDsum], iif(PlanTypeID="AVG",[MTDavg], 0))

You could use the formulae directly in the calculation, but it would be a
pretty big forumale then and very hard dto read and debug. Putting them into
their own columns makes it easier.

This works if you only have the two plan types: if you add more, it will
return 0, and you need to come back and add another column and then add
another iif condition.

Note that I'm not 100% sure this will work as expected in the one query - you
may need to create an intermediate query with the two column names mentioned
above and then another query using thos column values.