Need to sum conditional formulas in a query

[Gina]

Hi all! I have a query that is working just great with
one exception: I want the query to sum some fields based
on IIf formulas. My IIf formulas return a price based on
a part number and a quantity. I'll paste the query:

SELECT MACompAssy.ComponentPartNo, Sum([QuotationReptQry]!
[QtyQuotedxQtyPer]) AS TotalReqd, IIf([MACompAssy]!
[ComponentPartNo]="2029-GF-4-S0001" And [TotalReqd]>=2 And
[TotalReqd]<5,245.73,IIf([MACompAssy]![ComponentPartNo]
="2029-GF-4-S0001" And [TotalReqd]>=5 And [TotalReqd]
<10,161.35,IIf([MACompAssy]![ComponentPartNo]="2029-GF-4-
S0001" And [TotalReqd]>=10 And [TotalReqd]<15,133.04,IIf
([MACompAssy]![ComponentPartNo]="2029-GF-4-S0001" And
[TotalReqd]>=15 And [TotalReqd]<20,123.72,IIf([MACompAssy]!
[ComponentPartNo]="2029-GF-4-S0001" And [TotalReqd]
=20,119.1,0))))) AS [2029-GF-4-S0001], IIf([MACompAssy]!
[ComponentPartNo]="433-3728" And [TotalReqd]>=2 And
[TotalReqd]<4,529.2,IIf([MACompAssy]![ComponentPartNo]
="433-3728" And [TotalReqd]>=4 And [TotalReqd]<6,345,IIf
([MACompAssy]![ComponentPartNo]="433-3728" And [TotalReqd]

=6 And [TotalReqd]<10,283.45,IIf([MACompAssy]!

[ComponentPartNo]="433-3728" And [TotalReqd]>=10 And
[TotalReqd]<15,234.4,IIf([MACompAssy]![ComponentPartNo]
="433-3728" And [TotalReqd]=15,209.82,0))))) AS [433-3728]
FROM MAComponents INNER JOIN ((QuotationReptQry INNER JOIN
MAQuotes ON QuotationReptQry.QuoteSer = MAQuotes.QuoteSer)
INNER JOIN ((MAAssy INNER JOIN MACompAssy ON
MAAssy.AssyPartNo = MACompAssy.AssyPartNo) INNER JOIN
MALineItem ON MAAssy.AssyPartNo = MALineItem.AssyPartNo)
ON (MAQuotes.QuoteSer = MALineItem.QuoteSer) AND
(QuotationReptQry.AssyPartNo = MAAssy.AssyPartNo)) ON
(MAComponents.ComponentPartNo =
MACompAssy.ComponentPartNo) AND
(MAComponents.ComponentPartNo =
QuotationReptQry.ComponentPartNo)
GROUP BY MACompAssy.ComponentPartNo;

Is there any way to sum the results of all my IIf formulas
short of exporting the query to Excel and summing them
there?

Any help would be greatly appreciated! Thanks!

 

[Steve Schapel]

Gina,

I don't see any reason why it wouldn't work to simply enclose each of
the entire IIf expressions within Sum() functions.

However, I take the liberty to modify the Select clause to slightly
simplify it...

SELECT MACompAssy.ComponentPartNo, Sum([QuotationReptQry].
[QtyQuotedxQtyPer]) AS TotalReqd, Sum(IIf([MACompAssy].
[ComponentPartNo]="2029-GF-4-S0001",IIf([TotalReqd] Between 2 And
4,245.73,IIf([TotalReqd] Between 5 And 9,161.35,IIf([TotalReqd]
Between 10 And 14,133.04,IIf([TotalReqd] Between 15 And
19,123.72,IIf([TotalReqd]=20,119.1,0))))),0)) AS [2029-GF-4-S0001],
Sum(IIf([MACompAssy].[ComponentPartNo]="433-3728",IIf([TotalReqd]
Between 2 And 3,529.2,IIf([TotalReqd] Between 4 And 5,345,IIf
([TotalReqd] Between 6 And 9,83.45,IIf([TotalReqd] Between 10 And
14,234.4,IIf([TotalReqd]=15,209.82,0))))),0)) AS [433-3728]
FROM MAComponents INNER JOIN ((QuotationReptQry INNER JOIN
MAQuotes ON QuotationReptQry.QuoteSer = MAQuotes.QuoteSer)
INNER JOIN ((MAAssy INNER JOIN MACompAssy ON
MAAssy.AssyPartNo = MACompAssy.AssyPartNo) INNER JOIN
MALineItem ON MAAssy.AssyPartNo = MALineItem.AssyPartNo)
ON (MAQuotes.QuoteSer = MALineItem.QuoteSer) AND
(QuotationReptQry.AssyPartNo = MAAssy.AssyPartNo)) ON
(MAComponents.ComponentPartNo =
MACompAssy.ComponentPartNo) AND
(MAComponents.ComponentPartNo =
QuotationReptQry.ComponentPartNo)
GROUP BY MACompAssy.ComponentPartNo;

- Steve Schapel, Microsoft Access MVP

Hi all! I have a query that is working just great with
one exception: I want the query to sum some fields based
on IIf formulas. My IIf formulas return a price based on
a part number and a quantity. I'll paste the query:

SELECT MACompAssy.ComponentPartNo, Sum([QuotationReptQry]!
[QtyQuotedxQtyPer]) AS TotalReqd, IIf([MACompAssy]!
[ComponentPartNo]="2029-GF-4-S0001" And [TotalReqd]>=2 And
[TotalReqd]<5,245.73,IIf([MACompAssy]![ComponentPartNo]
="2029-GF-4-S0001" And [TotalReqd]>=5 And [TotalReqd]
<10,161.35,IIf([MACompAssy]![ComponentPartNo]="2029-GF-4-
S0001" And [TotalReqd]>=10 And [TotalReqd]<15,133.04,IIf
([MACompAssy]![ComponentPartNo]="2029-GF-4-S0001" And
[TotalReqd]>=15 And [TotalReqd]<20,123.72,IIf([MACompAssy]!
[ComponentPartNo]="2029-GF-4-S0001" And [TotalReqd]
=20,119.1,0))))) AS [2029-GF-4-S0001], IIf([MACompAssy]!
[ComponentPartNo]="433-3728" And [TotalReqd]>=2 And
[TotalReqd]<4,529.2,IIf([MACompAssy]![ComponentPartNo]
="433-3728" And [TotalReqd]>=4 And [TotalReqd]<6,345,IIf
([MACompAssy]![ComponentPartNo]="433-3728" And [TotalReqd]

=6 And [TotalReqd]<10,283.45,IIf([MACompAssy]!

[ComponentPartNo]="433-3728" And [TotalReqd]>=10 And
[TotalReqd]<15,234.4,IIf([MACompAssy]![ComponentPartNo]
="433-3728" And [TotalReqd]=15,209.82,0))))) AS [433-3728]
FROM MAComponents INNER JOIN ((QuotationReptQry INNER JOIN
MAQuotes ON QuotationReptQry.QuoteSer = MAQuotes.QuoteSer)
INNER JOIN ((MAAssy INNER JOIN MACompAssy ON
MAAssy.AssyPartNo = MACompAssy.AssyPartNo) INNER JOIN
MALineItem ON MAAssy.AssyPartNo = MALineItem.AssyPartNo)
ON (MAQuotes.QuoteSer = MALineItem.QuoteSer) AND
(QuotationReptQry.AssyPartNo = MAAssy.AssyPartNo)) ON
(MAComponents.ComponentPartNo =
MACompAssy.ComponentPartNo) AND
(MAComponents.ComponentPartNo =
QuotationReptQry.ComponentPartNo)
GROUP BY MACompAssy.ComponentPartNo;

Is there any way to sum the results of all my IIf formulas
short of exporting the query to Excel and summing them
there?

Any help would be greatly appreciated! Thanks!