Need to sum conditional formulas in a query

  • Thread starter Thread starter Gina
  • Start date Start date
G

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]!
Click to expand...
[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!
 
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]!
Click to expand...
[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!
Click to expand...
 
Back
Top