Stop Access from asking for parameters in Query

Access keeps asking for parameters

qTtlSalesbyParent!SumOfSales

My query

SELECT tblCMAP.Parent, 
Sum(tblCMAP.Sales) 
AS SumOfSales,
IIf(Nz([qTtlSalesbyParent]!SumOfSales],0)>=1000,20,
IIf([qTtlSalesbyParent]![SumOfSales]>=500,15,
IIf([qTtlSalesbyParent]![SumOfSales]>=100,10,
IIf([qTtlSalesbyParent]![SumOfSales]>=10,5,
IIf([qTtlSalesbyParent]![SumOfSales]<=9,0,0))))) AS TSalesPts
FROM tblCMAP
GROUP BY tblCMAP.Parent, IIf(Nz([qTtlSalesbyParent]!SumOfSales],0)>=1000,20,
IIf([qTtlSalesbyParent]![SumOfSales]>=500,15,
IIf([qTtlSalesbyParent]![SumOfSales]>=100,10,
IIf([qTtlSalesbyParent]![SumOfSales]>=10,5,
IIf([qTtlSalesbyParent]![SumOfSales]<=9,0,0)))));

And I have a calculated field in the query

TSalesPts: IIf(Nz([qTtlSalesbyParent]!SumOfSales],0)>=1000,20,
IIf([qTtlSalesbyParent]![SumOfSales]>=500,15,
IIf([qTtlSalesbyParent]![SumOfSales]>=100,10,
IIf([qTtlSalesbyParent]![SumOfSales]>=10,5,
IIf([qTtlSalesbyParent]![SumOfSales]<=9,0,0)))))

Everything seems to be working fine but I want it to stop asking for parameters and this query is attached to another query so the it trickles down.

Answer

I figured it out with help form a co-worker. I just needed to simplify the statement and change the “Group By’ in the design window to “Expression”. The new SQL sataement follows:

SELECT tblCMAP.Parent, 
Sum(tblCMAP.Sales) 
AS SumOfSales,
IIf(Nz([SumOfSales],0)>=1000,20,
IIf([SumOfSales]>=500,15,
IIf([SumOfSales]>=100,10,
IIf([SumOfSales]>=10,5,
IIf([SumOfSales]<=9,0,0))))) AS TSalesPts
FROM tblCMAP
GROUP BY tblCMAP.Parent;

The expression in the calculated field then remains the same.

Advertisement

Answer